Differences

This shows you the differences between two versions of the page.

--- oracle_errors [2018/12/06 21:05] – created 91.177.234.129
+++ oracle_errors [2024/06/28 13:36] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-====== Oracle_errors ======
+==== ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors ====
+  * Reference: [[htps://support.oracle.com/epmos/faces/DocumentDisplay?id=1507093.1|How to Identify ORA-00060 Deadlock Types Using Deadlock Graphs in Trace (Doc ID 1507093.1)]]
+Using this document shows the most common causes of deadlock and how to identify them.
-=====ORA-00600=====
+==== ORA-00600 ====
-[[https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=397490397952859&id=1092832.1&_afrWindowMode=0&_adf.ctrl-state=184vwc6g3y_4|Master Note for Diagnosing ORA-600 (Doc ID 1092832.1)]]
+  * [[https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=397490397952859&id=1092832.1&_afrWindowMode=0&_adf.ctrl-state=184vwc6g3y_4|Master Note for Diagnosing ORA-600 (Doc ID 1092832.1)]]
-=====ORA-19051: Cannot use fast path insert for this XMLType table=====
+  * [[https://support.oracle.com/epmos/faces/DocumentDisplay?id=7445.1|ORA-600/ORA-7445/ORA-700 Error Look-up Tool (Doc ID 153788.1)]]
+==== ORA-07445: exception encountered: core dump [PC:0xEE00] [SIGSEGV] [ADDR:0x7C7A03A67C7242A6] [PC:0xEE00] [Address not mapped to object] [] ====
+An ORA-0600 error is a 'handled' exception. An 07445 error is an unhandled exception.
+When a 7445 error occurs, there will always be a core dump. Check the alertlog for the trace files.
+<code>
+-05-01T14:04:22.421773+02:00
+Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x7C7A03A67C7242A6] [PC:0xEE00, {empty}] [flags: 0x0, count: 1]
+Errors in file /cln/exp/ora_bin1/app/oracle/diag/rdbms/cwm/cwm/trace/cwm_j002_44959600.trc  (incident=199498):
+ORA-07445: exception encountered: core dump [PC:0xEE00] [SIGSEGV] [ADDR:0x7C7A03A67C7242A6] [PC:0xEE00] [Address not mapped to object] []
+Incident details in: /cln/exp/ora_bin1/app/oracle/diag/rdbms/cwm/cwm/incident/incdir_199498/cwm_j002_44959600_i199498.trc
+</code>
+Use adrci to package up the problem.
+Also use stackx to grab a stack trace from the core dump file and send that to Oracle as well. [[https://support.oracle.com/epmos/faces/DocumentDisplay?id=1812.1|TECH: Getting a Stack Trace from a CORE file on Unix (Doc ID 1812.1)]]
+===== Solution =====
+Dumps did not provide a clear answer but Oracle suggest disabling the SQL Tuning Advisor
+<code>
+select client_name, status from dba_autotask_client;
+ CLIENT_NAME                           STATUS
+-------------------------------       ---------
+auto optimizer stats collection       ENABLED
+auto space advisor                    ENABLED
+sql tuning advisor                    ENABLED
+</code>
+<code>
+begin
+dbms_auto_task_admin.disable ( client_name => 'sql stats advisor'
+                             , operation   => null
+                             , window_name => null
+                             );
+end;
+/
+</code>
+or this should work also
+<code>
+exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');
+</code>
+<code>
+select client_name, status from dba_autotask_client;
+ CLIENT_NAME                           STATUS
+-------------------------------       ---------
+auto optimizer stats collection       DISABLED
+auto space advisor                    ENABLED
+sql tuning advisor                    ENABLED
+</code>
+==== ORA-01422: exact fetch returns more than requested number of rows in EM Cloud control GUI ====
+Updating Monitoring Configuration to changing database monitoring credentials via GUI fails with ORA-1422
+ * Cause
+Target was in not promoted  list. The following query show the target twice. One with status promoted and the other with status no promoted.
+<code>
+SET echo on
+SELECT chld.target_name, chld.target_type, count(*)
+  FROM em_targets prnt,
+  mgmt_assoc_instances inst,
+  mgmt_assoc_type_extends ext,
+  mgmt_targets agt,
+  mgmt_targets chld
+  WHERE prnt.target_guid = inst.source_me_guid
+  AND inst.dest_me_guid = chld.target_guid
+  AND inst.assoc_type = ext.assoc_type
+  AND ext.extended_assoc_type = 'app_composite_contains'
+  AND agt.emd_url = prnt.emd_url
+  AND agt.target_type = 'oracle_emd'
+  GROUP BY chld.target_name, chld.target_type HAVING count(*)>1
+  order by chld.target_name;
+</code>
+ * Solution
+Remove the duplicate target which was not promoted.
+Go to Setup -> Add Target -> Auto discover results -> Targets on Hosts () tab -> Look for the target and delete or ignore it.
+==== ORA-29280: invalid directory object ====
+A dba directory has been setup and read,write permissions granted on it to a user, the oracle user has the correct permissions at Unix level and still there are errors.
+<code>
+set serveroutput on
+declare
+    l_file utl_file.file_type;
+begin
+    l_file := utl_file.fopen ('iam_directory_file_output', 'some_new_file_name.txt', 'w');
+    utl_file.put_line (l_file, 'content of the file');
+    utl_file.fclose (l_file);
+end;
+</code>
+<code>
+declare
+*
+ERROR at line 1:
+ORA-29283: invalid file operation: cannot open file [29435]
+ORA-06512: at "SYS.UTL_FILE", line 41
+ORA-06512: at "SYS.UTL_FILE", line 478
+ORA-06512: at line 4
+</code>
+If users will be accessing the directory over tns, the listener needs to be restarted (reloading is not good enough) to pick up the new directory and its grants!
+There is an asktom thread about it here [[https://asktom.oracle.com/pls/apex/asktom.search?tag=ora-29283-invalid-file-operation|ORA-29283 Invalid file operation - asktom.com]]
+==== exec(): 0509-036 Cannot load program sqlplus because of the following errors: ====
+<code>
+exec(): 0509-036 Cannot load program sqlplus because of the following errors:
+rtld: 0712-001 Symbol CreateIoCompletionPort was referenced
+      from module /cln/dev/ora_bin/app/oracle/product/12.1.0.2/dbhome_1/lib/libttsh12.so(), but a runtime definition
+      of the symbol was not found.
+rtld: 0712-001 Symbol GetMultipleCompletionStatus was referenced
+      from module /cln/dev/ora_bin/app/oracle/product/12.1.0.2/dbhome_1/lib/libttsh12.so(), but a runtime definition
+      of the symbol was not found.
+rtld: 0712-002 fatal error: exiting.
+</code>
+=== Cause ===
+This is caused by the I/O Completion Ports being set to "Defined" instead of "Available"
+=== Solution ===
+  * https://docs.oracle.com/en/database/oracle/oracle-database/12.2/axcli/enabling-io-completion-ports.html#GUID-9907E8AC-A260-4369-8BF6-EE5BED434FD2
+To check if the IOCP module is enabled, run the lsdev command:
+<code>
+$ lsdev | grep iocp
+</code>
+The following sample output shows the IOCP status is set to Defined and hence not enabled:
+<code>
+iocp0 Defined I/O Completion Ports
+</code>
+By default, IOCP is set to Defined. To enable IOCP, set IOCP to Available using the following procedure:
+Log in as root and run the following command:
+<code>
+# smitty iocp
+</code>
+Select Change / Show Characteristics of I/O Completion Ports.
+Change configured state at system restart from Defined to Available.
+Run the lsdev command to confirm the IOCP status is set to Available:
+<code>
+$ lsdev | grep iocp
+iocp0 Available I/O Completion Ports
+</code>
+Restart the system to make the changes permanent
+==== Autotask not running gather_stats on some databases ====
+ref: [[https://oracle-base.com/articles/11g/automated-database-maintenance-task-management-11gr1|automated-database-maintenance-task-management]]
+=== Problem: ===
+No errors shown anywhere but running a query to see last_analysed date shows nothing being done recently.
+<code>
+select count('e'), trunc(last_analyzed) from dba_tables group by trunc(last_analyzed) order by 2 desc
+/
+</code>
+<code>
+COUNT('E') TRUNC(LAST_ANALYZED)
+---------- ---------------------------
+
+08-FEB-21 00:00:00
+31-AUG-20 00:00:00
+28-AUG-20 00:00:00
+26-AUG-20 00:00:00
+25-AUG-20 00:00:00
+04-JUL-20 00:00:00
+03-JUL-20 00:00:00
+14-JUN-20 00:00:00
+12-JUN-20 00:00:00
+10-JUN-20 00:00:00
+08-JUN-20 00:00:00
+</code>
+So something happened in June to stop the stats from being gathered.
+There is a whole host of things to check:
+  * maintenance window definitions
+  * maintenance window schedules
+  * whether the autotask jobs are enabled
+  * whether the autotask jobs are enabled in the window
+Checked all of these and everything was ok.
+Turned out to be the job_queue_processes database parameter. It was set too low so there was simply no processes available within the window to allow the 'auto optimizer stats collection' to run.
+=== Solution: ===
+Tune the job_queue_processes parameter. It should be set to the lower of these 2 values
+<code>
+select c.value*20 based_on_cpu, s.value/4 based_on_sessions from v$parameter c, v$parameter s where c.name = 'cpu_count' and s.name = 'sessions'
+/
+</code>
+<code>
+alter system set job_queue_processes = 387 scope=both;
+System altered.
+</code>
+==== EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet ====
+Error when uploading from an agent
+<code>
+emctl upload agent
+Oracle Enterprise Manager Cloud Control 13c Release 5
+Copyright (c) 1996, 2021 Oracle Corporation.  All rights reserved.
+---------------------------------------------------------------
+EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet. If this issue persists check trace files for ping to OMS related errors.
+</code>
+Solution
+<code>
+emctl stop agent
+emctl unsecure agent
+emctl secure agent
+emctl clearstate agent
+</code>
+Try again
+<code>
+emctl upload agent
+Oracle Enterprise Manager Cloud Control 13c Release 5
+Copyright (c) 1996, 2021 Oracle Corporation.  All rights reserved.
+---------------------------------------------------------------
+EMD upload completed successfully
+</code>
+Good explanation of possible causes in MOS document [[https://support.oracle.com/epmos/faces/DocumentDisplay?id=1538717.1|Alert.log shows ORA-609 with TNS-12537: TNS:connection closed (Doc ID 1538717.1)]]
+<code>
+Fatal NI connect error 12537, connecting to:
+ (LOCAL=NO)
+  VERSION INFORMATION:
+	TNS for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production
+	TCP/IP NT Protocol Adapter for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production
+	Oracle Bequeath NT Protocol Adapter for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production
+  Time: 21-APR-2021 06:17:44
+  Tracing not turned on.
+  Tns error struct:
+    ns main err code: 12537
+TNS-12537: TNS:connection closed
+    ns secondary err code: 12560
+    nt main err code: 0
+    nt secondary err code: 0
+    nt OS err code: 0
+Wed Apr 21 06:17:44 2021
+opiodr aborting process unknown ospid (65536354) as a result of ORA-609
+</code>
+==== runInstaller fails with "[FATAL] [INS-30060] Check for group existence failed. ====
+<code>
+[WARNING] [INS-13001] Oracle Database is not supported on this operating system. Installer will not perform prerequisite checks on the system.
+  CAUSE: This operating system may not have been in the certified list at the time of the release of this software.
+  ACTION: Refer to My Oracle Support portal for the latest certification information for this operating system. Proceed with the installation if the operating system has been certified after the release of this software.
+[WARNING] [INS-32056] The specified Oracle Base contains the existing Central Inventory location: /db_home/oracle/product/oraInventory.
+  ACTION: Oracle recommends that the Central Inventory location is outside the Oracle Base directory. Specify a different location for the Oracle Base.
+[FATAL] [INS-30060] Check for group existence failed.
+  CAUSE: Unexpected error occurred while trying to check for group existence.
+  ACTION: Refer to the logs or contact Oracle Support Services. Note for advanced users: Launch the installer by passing the following flag '-ignoreInternalDriverError'.
+</code>
+According to Doc ID 2464358.1, this can be ignored (in this case) as we know 19.16 is certified on RedHat 6.1.
+=== Solution ===
+Add option '-ignoreInternalDriverError' to the runInstaller command line.
+==== dbca - [FATAL] [DBT-50000] Unable to check for available memory. ====
+dbca fails with this odd error before attempting to create the databae.\\
+<code>
+./dbca -silent -createDatabase                                                   \
+>      -gdbname olmdb -sid  olmdb -responseFile NO_VALUE         \
+>       -templateName General_Purpose.dbc                                         \
+>      -characterSet AL32UTF8                                                    \
+>      -sysPassword Welcome1                                                 \
+>      -systemPassword Welcome1                                              \
+>      -createAsContainerDatabase true                                           \
+>      -pdbAdminPassword Welcome1                                            \
+>      -databaseType MULTIPURPOSE                                                \
+>      -datafileDestination "/opt/oradata"                                        \
+>      -memoryMgmtType auto_sga                                                  \
+>      -totalMemory 2000                                                         \
+>      -storageType FS                                                           \
+>      -datafileDestination "/opt/oradata"                                        \
+>      -redoLogFileSize 50                                                       \
+>      -emConfiguration NONE                                                     \
+>      -ignorePreReqs
+[FATAL] [DBT-50000] Unable to check for available memory.
+[FATAL] [DBT-50001] Unable to check the value of kernel parameter {0}
+</code>
+=== Solution ===
+Enable x11-forwarding and to mount /tmp in exec mode, this normally in noexec (otherwise you will get an error in dbca when reading the memory of the server)
+or export TMP to a directory that has exec privilege.
+==== trace directory contains very large .trc files containing SQL*Loader (sqlldr) AWR data ====
+Trace file contains a file of almost 7Gb
+<code>
+-rw-rw----    1 oracle   oinstall       1042 Oct 13 11:04 portala_ora_58918122.trc
+-rw-rw----    1 oracle   oinstall         66 Oct 13 12:01 portala_ora_39977800.trm
+-rw-rw----    1 oracle   oinstall        920 Oct 13 12:01 portala_ora_39977800.trc
+-rw-r-----    1 oracle   oinstall      59550 Oct 13 12:01 alert_portala.log
+-rw-rw----    1 oracle   oinstall         76 Oct 13 12:04 portala_ora_12714240.trm
+-rw-rw----    1 oracle   oinstall       1070 Oct 13 12:04 portala_ora_12714240.trc
+-rw-r-----    1 oracle   oinstall     738682 Oct 13 12:52 portala_mmnl_15859950.trm
+-rw-r-----    1 oracle   oinstall 6780484687 Oct 13 12:52 portala_mmnl_15859950.trc
+drwxr-xr-x    3 oracle   oinstall     548864 Oct 13 12:56 .
+-rw-r-----    1 oracle   oinstall        335 Oct 13 12:56 portala_ora_59966554.trm
+-rw-r-----    1 oracle   oinstall       7223 Oct 13 12:56 portala_ora_59966554.trc
+-rw-rw-r--    1 oracle   dba         3228704 Oct 13 12:56 sbtio.log
+</code>
+The AWR collection background process mmnl created this trace file.
+Checking on the filesystem we can see the process is not running for this database
+<code>
+ps -ef|grep "${ORACLE_SID}"|grep mmnl
+</code>
+Check when the last AWR snapshots were created (default is every hour)
+<code>
+col instance_number     format 999  heading 'Inst'
+col startup_time        format a15  heading 'Startup'
+col begin_interval_time format a15  heading 'Begin snap'
+col end_interval_time   format a15  heading 'End Snap'
+col flush_elapsed       format a20  heading 'flush elapsed'
+col error_count         format 9999 heading 'Err#'
+select *
+from
+(
+    select instance_number
+    ,      startup_time
+    ,      begin_interval_time
+    ,      end_interval_time
+    ,      flush_elapsed
+    ,      error_count
+    from   dba_hist_snapshot
+    order  by begin_interval_time desc
+)
+where  rownum < 5
+/
+</code>
+According to "MMNL Background Process Creates Large Trace Files Containing ASH Information (Doc ID 1952274.1)" and "Missing Automatic Workload Repository (AWR) Snapshots and Other Collection Issues (Doc ID 1301503.1)", our case here is tha fact that the background process no longer exists.
+Leading to "AWR Snapshots Are Not Being Created Because MMON Is Not Being Respawned (Doc ID 2023652.1)", we can restart the process like this
+<code>
+alter system enable restricted session;
+alter system disable restricted session;
+</code>
+If this fails to restart the process, then an instance restart should do it.
+==== ORA-19051: Cannot use fast path insert for this XMLType table ====
 Think LD_LIBRARY_PATH on Linux or LIBPATH on AIX.
-<code>0@@</code>
+<code>
-====Problem====
+Export: Release 11.2.0.4.0 - Production on Thu Jun 1 06:41:55 2017
+Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
+Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
+With the Partitioning, OLAP, Data Mining and Real Application Testing options
+Starting "DWH_EXPORT"."SYS_EXPORT_TABLE_03":  dwh_export/******** parfile=cadis_foto_export_credit_dba.par
+Estimate in progress using BLOCKS method...
+Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
+Total estimation using BLOCKS method: 64.08 GB
+ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [[]]
+ORA-19051: Cannot use fast path insert for this XMLType table
+ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
+ORA-06512: at "SYS.KUPW$WORKER", line 9710
+</code>
+=== Problem ===
 When the 11g DB was started, LD_LIBRARY_PATH and LIBPATH were still pointing to paths in the 10g Home.
-====Solution====
+=== Solution ===
 There may have been some odd values setup in LD_LIBRARY_PATH before the call to oraenv (if oraenv was indeed called!)
-<code>1@@</code>
+<code>
+unset LD_LIBRARY_PATH LIBPATH
+export ORACLE_SID=<correct 11g SID>
+. oraenv
+echo $LD_LIBRARY_PATH
+echo $LIBPATH
+</code>
 They should now be correct.
-=====ORA-27092: size of file exceeds file size limit of the process=====
+==== ORA-39346: data loss in character set conversion for object TABLE_STATISTICS: ====
+When importing data from a .dmp file using Data Pump, get these errors
+<code>
+ORA-39346: data loss in character set conversion for object TABLE_STATISTICS:"<owner>"."<table_name>"
+. . imported "LIG_DATA_DBA"."BATCH_DEMANDE"                  0 KB       0 rows
+. . imported "LIG_DATA_DBA"."GARANTIE_CONTRATS"          7.937 MB  213359 rows
+Processing object type SCHEMA_EXPORT/VIEW/VIEW
+Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
+Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
+Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
+Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
+Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
+ORA-39346: data loss in character set conversion for object TABLE_STATISTICS:"LIG_DATA_DBA"."GARANTIE"
+ORA-39346: data loss in character set conversion for object TABLE_STATISTICS:"LIG_DATA_DBA"."CLIENT_IDJS"
+ORA-39346: data loss in character set conversion for object TABLE_STATISTICS:"LIG_DATA_DBA"."IDJ"
+ORA-39346: data loss in character set conversion for object TABLE_STATISTICS:"LIG_DATA_DBA"."TIERS"
+ORA-39346: data loss in character set conversion for object TABLE_STATISTICS:"LIG_DATA_DBA"."MOUVEMENT"
+</code>
+=== Solution ===
+<code>
+exec dbms_stats.gather_table_stats ('<OWNER>', '<TABLE NAME>', method_opt => 'FOR ALL COLUMNS');
+exec dbms_stats.gather_table_stats('LIG_DATA_DBA','GARANTIE', method_opt => 'FOR ALL COLUMNS');
+exec dbms_stats.gather_table_stats('LIG_DATA_DBA','CLIENT_IDJS', method_opt => 'FOR ALL COLUMNS');
+exec dbms_stats.gather_table_stats('LIG_DATA_DBA','IDJ', method_opt => 'FOR ALL COLUMNS');
+exec dbms_stats.gather_table_stats('LIG_DATA_DBA','TIERS', method_opt => 'FOR ALL COLUMNS');
+</code>
+==== ORA-20247: EMD_URL is invalid:  Cannot provide null emd url. ====
+When on the oms and attempting to add_target with an argfile, this cryptic error shows up
+<code>
+emcli argfile /tmp/emcli_add_targets.argfile
+Error: Cannot save the target to the agent
+ORA-20247: EMD_URL is invalid:  Cannot provide null emd url.
+Error: Cannot save the target to the agent
+ORA-20247: EMD_URL is invalid:  Cannot provide null emd url.
+Error: Cannot save the target to the agent
+ORA-20247: EMD_URL is invalid:  Cannot provide null emd url.
+Error: Cannot save the target to the agent
+ORA-20247: EMD_URL is invalid:  Cannot provide null emd url.
+</code>
+Two things to check
+  * hostname is incorrect in the argfile. Does it have the domain on it? Is it a cluster name instead of a node name?
+  * was it a recent agent install or an upgrade? Check the host was added. From the host:
+<code>
+emctl secure agent
+emctl status agent
+</code>
+How many targets are monitored? Let's see what they are
+<code>
+emctl config agent listtargets
+</code>
+If only the oracle_emd is mentioned and not the host, add it
+<code>
+emctl config agent addInternaltargets
+emctl upload agent
+emctl status agent
+</code>
+Now go back to the oms server and add the targets again. Should work now. See also [[https://support.oracle.com/epmos/faces/DocumentDisplay?id=1934665.1|Agent and Host Targets not Shown in the Enterprise Manager Cloud Control]]
+==== ORA-27092: size of file exceeds file size limit of the process ====
 Think ulimit...
-<code>2@@</code>
+<code>
-<code>3@@</code>
+Cause: an attempt was made to open a file that exceeds the process's file size limit (ulimit), additional information shows the current limit (logical blocks) and the size of the file (logical blocks)
-Check the fs_size limit for this user in /etc/security/limits<br />
+Action: increase the processes file size limit (ulimit) and retry
+</code>
+<code>
+truncate table pibadm.test_ph
+                      *
+Error at line 1 :
+ORA-00604: error ocurred at recursive SQL level 1
+ORA-01116: error in opening database file 2
+ORA-01110: datafile 2 : '/opt/u01/data/PIBQA/UNDO_01.dbf'
+ORA-27092: size of file exceeds file size limit of the process
+Additional information: 262143
+Additional information: 2621441
+</code>
+Check the fs_size limit for this user in /etc/security/limits\\
 Compare this with another user, say oracle. Probably -1 (unlimited)
-=====SP2-1503: Unable to initialize Oracle call interface=====
+==== ORA-01157: cannot identify/lock data file 201 - see DBWR trace file ====
+After restore or duplicate database, TEMP tablespace gives errors
+<code>
+SQL> select * from dba_temp_files;
+select * from dba_temp_files
+              *
+ERROR at line 1:
+ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
+ORA-01110: data file 201: '/cln/acc/ora_data3/credoa/credoa/temp01.dbf'
+</code>
+But we can still select from v$tempfile
+<code>
+SQL> select file#,name from v$tempfile;
+     FILE# NAME
+---------- --------------------------------------------------------------------------------
+/cln/acc/ora_data3/credoa/credoa/temp01.dbf
+</code>
+This entry can be removed from the controlfile as the file does not actually exist on disk
+<code>
+SQL> alter database tempfile '/cln/acc/ora_data3/credoa/credoa/temp01.dbf' drop;
+Database altered.
+SQL> select file#,name from v$tempfile;
+no rows selected
+</code>
+TEMP tablespace exists but has no tempfiles in it, add one
+<code>
+SQL> alter tablespace temp add tempfile '/cln/acc/ora_data3/credoa/temp01.dbf' size 1g autoextend on maxsize unlimited;
+Tablespace altered.
+</code>
+==== SP2-1503: Unable to initialize Oracle call interface ====
 When trying to login to the database via SQL*Plus, these messages a displayed
-<code>4@@</code>
+<code>
-Seems to be a problem unique to AIX (in this case).<br />
+hn5118 /home/oracle $sqlplus / as sysdba
+SP2-1503: Unable to initialize Oracle call interface
+SP2-0152: ORACLE may not be functioning properly
+</code>
+Seems to be a problem unique to AIX (in this case).\\
 Check to see what the LIBPATH variable is. It may still contain a path to an old ORACLE_HOME...
-<code>5@@</code>
+<code>
+hn5118 /home/oracle $env|grep LIBPATH
+LIBPATH=/oracle/exp/ora_bin1/app/oracle/product/10.2.0/db_1/lib:/oracle/exp/ora_bin1/app/oracle/product/10.2.0/db_1/lib
+</code>
 Disco! Unset it and rerun.
-<code>6@@</code>
+<code>
+hn5118 /home/oracle $unset LIBPATH
-=====UDE-31623: operation generated ORACLE error 31623=====
+hn5118 /home/oracle $sqlplus / as sysdba
+SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 12 17:17:54 2017
+Copyright (c) 1982, 2014, Oracle.  All rights reserved.
+Connected to:
+Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
+With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
+SQL>
+</code>
+==== UDE-31623: operation generated ORACLE error 31623 ====
 When using Data pump, get following errors almost immediately:
-<code>7@@</code>
+<code>
-**Solution 1:**<br />
+UDE-31623: operation generated ORACLE error 31623
-In one case, I simplified the parameter file that was used. Maybe related to Streams Pool size, who knows?!<br />
+ORA-31623: a job is not attached to this session via the specified handle
+ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
+ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
+ORA-06512: at line 1
+</code>
+**Solution 1:**\\
+In one case, I simplified the parameter file that was used. Maybe related to Streams Pool size, who knows?!\\
 I removed these 2 lines and the export started working:
-<code>8@@</code>
+<code>
-**Solution 2:**<br />
+compression_algorithm=medium
+logtime=all
+</code>
+**Solution 2:**\\
 There is a note about this on Metalink, 1080775.1 which suggests that Streams Pool Size needs setting.
-<code>9@@</code>
+<code>
+For this DataPump error, you will need to configure the database with some Streams Pool.
+Perform the following query to find out how much STREAMS_POOL_SIZE has been allocated
+ select * from v$sgainfo;
+ ...
+ Streams Pool Size                               0 Yes
+Data Pump now uses Advanced Queuing (AQ) internally, so it can handle job stops/starts/re-starts.  However, AQ using the Streams Pool, where the database currently has the STREAMS_POOL_SIZE set to ZERO.
+Manually set the STREAMS_POOL_SIZE (using ALTER SYSTEM or by changing the value in the the PFILE/SPFILE), re-start the database and re-attempt the Data Pump Export.
+Alternately, turn on Automatic Shared Memory management (ASMM) by setting SGA_TARGET or MEMORY_TARGET.
+</code>
 From Oracle Documentation
-<code>10@@</code>
+<code>
+Setting the Size Of the Buffer Cache In a Streams Environment
+Oracle Data Pump uses Streams functionality to communicate between processes.
+If the SGA_TARGET initialization parameter is set, then the STREAMS_POOL_SIZE initialization parameter is automatically set to a reasonable value.
+If the SGA_TARGET initialization parameter is not set and the STREAMS_POOL_SIZE initialization parameter is not defined,
+then the size of the streams pool automatically defaults to 10% of the size of the shared pool.
+When the streams pool is created, the required SGA memory is taken from memory allocated to the buffer cache,
+reducing the size of the cache to less than what was specified by the DB_CACHE_SIZE initialization parameter.
+This means that if the buffer cache was configured with only the minimal required SGA, then Data Pump operations may not work properly.
+A minimum size of 10M is recommended for STREAMS_POOL_SIZE in order to ensure successful Data Pump operations.
+</code>
-=====ORA-00020: maximum number of processes (150) exceeded=====
+==== ORA-00020: maximum number of processes (150) exceeded ====
 Check the current resource limits with:
-<code>11@@</code>
+<code>
-Unfortunately this limit cannot be increased without a restart.<br />
+set lines 300
+select * from v$resource_limit;
+</code>
+Unfortunately this limit cannot be increased without a restart.\\
 It may be a necessary to increase the limit with
-<code>12@@</code>
+<code>
-followed by a shutdown/startup<br />
+alter system set processes=500 scope=spfile;
-Or it may be some user is running away with the limit.<br />
+</code>
+followed by a shutdown/startup\\
+Or it may be some user is running away with the limit.\\
 Check to see who is running what with:
-[[http://www.mailnest.com/dbawiki/index.php/Handy_scripts#See_what_statements_all_users_are_running See_what_statements_all_users_are_running]]
+[[http://www.mailnest.com/dbawiki/index.php/Handy_scripts#See_what_statements_all_users_are_running|See_what_statements_all_users_are_running]]
-=====ORA-01555: snapshot too old: rollback segment number nn with name "xxx" too small=====
+==== ORA-01555: snapshot too old: rollback segment number nn with name "xxx" too small ====
-  * [[http://dfitzjarrell.wordpress.com/2008/02/22/the-dreaded-ora-01555/|Good explanation of why it happens]]<br />
+  *  [[http://dfitzjarrell.wordpress.com/2008/02/22/the-dreaded-ora-01555/|Good explanation of why it happens]]
-<br />
+=== Problem ===
-====Problem====
 A long running transaction has run out of space to store its read-consistent image
-====Solution 1====
+=== Solution 1 ===
 If it's a one-off, maybe leave it alone. It may have been a user leaving a session open overnight without committing or rolling back.
-====Solution 2====
+=== Solution 2 ===
 Make sure there's enough space to allow the transaction to finish.
-====Things to check====
+=== Things to check ===
 How much undo did the biggest transaction take?
 What is the undo retention parameter set to?
-<code>13@@</code>
+<code>
+set lines 200
+col name        for a30
+col description for a50
+col value       for a20
+col maxquerylen for 999999
+select p.name
+,      p.description
+,      p.value
+,      max(u.maxquerylen) maxquerylen
+from   v$parameter p
+,      v$undostat  u
+where  1=1
+and    p.name = 'undo_retention'
+group  by p.name
+,      p.description
+,      p.value;
+</code>
 Set the retention to about 20% bigger than the maxquerylen.
-<code>14@@</code>
+<code>
+select 'alter system set undo_retention='||round(1.2*max(maxquerylen))||' scope=both;' "Run this" from v$undostat;
+</code>
 and set the undo retention guarantee (being aware of the consequences)
-<code>15@@</code>
+<code>
-<code>16@@</code>
+select tablespace_name
+,      retention
+from   dba_tablespaces;
+</code>
+<code>
+alter tablespace &undo_ts_name retention guarantee;
+</code>
-=====Optimise Oracle UNDO Parameters=====
+==== Optimise Oracle UNDO Parameters ====
-The ideal undo_retention would be enough to hold rollback for the longest transaction that happened so far (can't see into the future)<br />
-So current undo_retention = current value of undo / (db_block_size * undo blocks per sec)<br />
+The ideal undo_retention would be enough to hold rollback for the longest transaction that happened so far (can't see into the future)\\
-  * Undo Blocks per Second
+So current undo_retention = current value of undo / (db_block_size * undo blocks per sec)\\
-<code>17@@</code>
+  *  Undo Blocks per Second
+<code>
+SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCKS_PER_SEC"
+FROM   v$undostat;
+</code>
 Optimal Undo Retention
-<code>18@@</code>
+<code>
-  * Calculate Needed UNDO Size for given Database Activity
+col actual for 9999999999.99 heading "ACTUAL UNDO SIZE [[MByte]]"
-<code>19@@</code>
+col retent for 9999999999    heading "UNDO RETENTION [[Sec]]"
-It might be that the Export still fails with ORA-01555 even though retention seems long enough.<br />
+col needed for 9999999999.99 heading "NEEDED UNDO SIZE [[Secs]]"
-Are you running an export with the flashback_time parameter? If so, the undo_retention should be longer than the total export running time!<br />
-=====Check LOB undo retention=====
+SELECT d.undo_size/(1024*1024) actual,
-The retention settings for LOBS is different for the rest of the database.<br />
+       to_number(SUBSTR(e.value,1,25)) retent,
-When you change the UNDO_RETENTION parameter, the LOB segment’s retention value is NOT modified.<br />
+       ROUND((d.undo_size / (to_number(f.value) *
-If you query the RETENTION column of the DBA_LOBS view, you will notice the old UNDO_RETENTION value still remains after you have modified the UNDO_RETENTION parameter.<br />
+       g.undo_block_per_sec))) needed
-<code>20@@</code>
+  FROM (
-<code>21@@</code>
+       SELECT SUM(a.bytes) undo_size
-<code>22@@</code>
+          FROM v$datafile a,
+               v$tablespace b,
+               dba_tablespaces c
+         WHERE c.contents = 'UNDO'
+           AND c.status = 'ONLINE'
+           AND b.name = c.tablespace_name
+           AND a.ts# = b.ts#
+       ) d,
+       v$parameter e,
+       v$parameter f,
+       (
+       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
+              undo_block_per_sec
+         FROM v$undostat
+       ) g
+WHERE e.name = 'undo_retention'
+  AND f.name = 'db_block_size'
+/
+</code>
+  *  Calculate Needed UNDO Size for given Database Activity
+<code>
+col actual for 9999999999.99 heading "ACTUAL UNDO SIZE [[MByte]]"
+col retent for 9999999999    heading "UNDO RETENTION [[Sec]]"
+col needed for 9999999999.99 heading "NEEDED UNDO SIZE [[MByte]]"
+SELECT d.undo_size/(1024*1024) actual
+,      to_number(SUBSTR(e.value,1,25)) retent
+,      (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
+       g.undo_block_per_sec) / (1024*1024)  needed
+  FROM (
+       SELECT SUM(a.bytes) undo_size
+         FROM v$datafile a,
+              v$tablespace b,
+              dba_tablespaces c
+        WHERE c.contents = 'UNDO'
+          AND c.status = 'ONLINE'
+          AND b.name = c.tablespace_name
+          AND a.ts# = b.ts#
+       ) d,
+      v$parameter e,
+       v$parameter f,
+       (
+       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
+         undo_block_per_sec
+         FROM v$undostat
+       ) g
+ WHERE e.name = 'undo_retention'
+  AND f.name = 'db_block_size'
+/
+</code>
+It might be that the Export still fails with ORA-01555 even though retention seems long enough.\\
+Are you running an export with the flashback_time parameter? If so, the undo_retention should be longer than the total export running time!\\
+==== Check LOB undo retention ====
+The retention settings for LOBS is different for the rest of the database.\\
+When you change the UNDO_RETENTION parameter, the LOB segment’s retention value is NOT modified.\\
+If you query the RETENTION column of the DBA_LOBS view, you will notice the old UNDO_RETENTION value still remains after you have modified the UNDO_RETENTION parameter.\\
+<code>
+SQL> show parameter undo_
+NAME                                 TYPE        VALUE
+------------------------------------ ----------- ------------------------------
+undo_management                      string      AUTO
+undo_retention                       integer     36000
+undo_tablespace                      string      UNDOTBS1
+</code>
+<code>
+SQL> desc SITEADMIN.AUDIT_LOG_MESSAGE
+ Name                                      Null?    Type
+ ----------------------------------------- -------- ----------------------------
+ AUDIT_LOG_MESSAGE_ID                      NOT NULL NUMBER
+ AUDIT_LOG_ID                              NOT NULL NUMBER
+ SEQUENCE                                           NUMBER
+ MESSAGE                                            CLOB
+ CREATE_DATE                                        TIMESTAMP(6)
+ COMPONENT_ID                                       VARCHAR2(50 CHAR)
+</code>
+<code>
+set lines 300
+col column_name for a32
+select table_name,column_name, pctversion,retention from dba_lobs where owner='&table_owner';
+TABLE_NAME                COLUMN_NAME                    PCTVERSION  RETENTION
+------------------------- ------------------------------ ---------- ----------
+EMAIL_STORE               CONTENT                                 0
+EMAIL_STORE_ATTACHEMENT   ATTACHEMENT                             0
+TRACKING                  ORIGINAL_MESSAGE                                 900
+TRACKING                  MESSAGE                                          900
+TRACKING                  SIEBEL_MESSAGE                                   900
+WISE_WMS_LOG              WISE_WMS_XML                                     900
+ATTACHMENT                ORIGINAL_MESSAGE                                 900
+PLAN_TABLE                OTHER_XML                                        900
+AUDIT_LOG_MESSAGE         MESSAGE                                          900
+EXCEPTIONS                STACK_TRACE                                      900
+</code>
 To change the LOB segment’s RETENTION value to match the new UNDO_RETENTION value, do the following:
-<code>23@@</code>
+<code>
-By momentarily changing the LOB to use PCTVERSION and then back to RETENTION, the new value of UNDO_RETENTION will be used.<br />
+ALTER TABLE my_table MODIFY LOB (lob_column) (PCTVERSION 20);
+ALTER TABLE my_table MODIFY LOB (lob_column) (RETENTION);
+</code>
+By momentarily changing the LOB to use PCTVERSION and then back to RETENTION, the new value of UNDO_RETENTION will be used.\\
 You need to do this for all LOB segments that you intend to modify.
-<code>24@@</code>
+<code>
+select 'ALTER TABLE '||owner||'.'||table_name||' MODIFY LOB ('||column_name||') (PCTVERSION 20);' from dba_lobs where owner='TIBCO'
+select 'ALTER TABLE '||owner||'.'||table_name||' MODIFY LOB ('||column_name||') (RETENTION);'     from dba_lobs where owner='TIBCO';
+</code>
 There is a table DBA_LOBS that displays information about all the LOB columns in the database, but it doesn't actually store any LOB data, just metadata. The actual LOB segments in the database have system-generated names that take the form SYS_LOB*identifier*$$.
 For example if you identify a segment named SYS_LOBidentifier$$ that is consuming space, you can find out what column of what table that LOB column maps to using the DBA_LOBS table:
-<code>25@@</code>
+<code>
+set lines 1000
+col owner       for a20
+col table_name  for a32
+col column_name for a32
+select owner
+,      table_name
+,      column_name
+from   dba_lobs
+where  1=1
+and    segment_name = 'SYS_LOB<<identifier>>$$'
+</code>
-====The Undo Advisor PL/SQL Interface====
+=== The Undo Advisor PL/SQL Interface ===
-You can activate the Undo Advisor by creating an undo advisor task through the advisor framework.<br />
+You can activate the Undo Advisor by creating an undo advisor task through the advisor framework.\\
-The following example creates an undo advisor task to evaluate the undo tablespace. The name of the advisor is 'Undo Advisor'.<br />
+The following example creates an undo advisor task to evaluate the undo tablespace. The name of the advisor is 'Undo Advisor'.\\
-The analysis is based on Automatic Workload Repository snapshots, which you must specify by setting parameters START_SNAPSHOT and END_SNAPSHOT.<br />
+The analysis is based on Automatic Workload Repository snapshots, which you must specify by setting parameters START_SNAPSHOT and END_SNAPSHOT.\\
 In the following example, the START_SNAPSHOT is "1" and END_SNAPSHOT is "2".
-<code>26@@</code>
+<code>
-After you have created the advisor task, you can view the output and recommendations in the Automatic Database Diagnostic Monitor in Enterprise Manager.<br />
+DECLARE
+   tid    NUMBER;
+   tname  VARCHAR2(30);
+   oid    NUMBER;
+BEGIN
+   DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task');
+   DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, 'null', oid);
+   DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid);
+   DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 1);
+   DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 2);
+   DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'INSTANCE', 1);
+   DBMS_ADVISOR.execute_task(tname);
+        END;
+/
+</code>
+After you have created the advisor task, you can view the output and recommendations in the Automatic Database Diagnostic Monitor in Enterprise Manager.\\
 This information is also available in the DBA_ADVISOR_* data dictionary views (DBA_ADVISOR_TASKS, DBA_ADVISOR_OBJECTS, DBA_ADVISOR_FINDINGS, DBA_ADVISOR_RECOMMENDATIONS, and so on).
-====References====
+=== References ===
-  * [[http://www.akadia.com/services/ora_optimize_undo.html|akadia.com]]
+  *  [[http://www.akadia.com/services/ora_optimize_undo.html|akadia.com]]
-  * [[http://dfitzjarrell.wordpress.com/2008/02/22/the-dreaded-ora-01555/|the dreaded ora-01555]]
+  *  [[http://dfitzjarrell.wordpress.com/2008/02/22/the-dreaded-ora-01555/|the dreaded ora-01555]]
-  * [[http://docs.oracle.com/cd/B28359_01/server.111/b28310/undo004.htm#ADMIN11469|docs.oracle.com]]
+  *  [[http://docs.oracle.com/cd/B28359_01/server.111/b28310/undo004.htm#ADMIN11469|docs.oracle.com]]
-=====Error 6 initializing SQL*Plus=====
+==== Error 6 initializing SQL*Plus ====
-Using sqlplus or rman under Cygwin, ORACLE_HOME still needs to be windows format!<br />
+Using sqlplus or rman under Cygwin, ORACLE_HOME still needs to be windows format!\\
-<code>27@@</code>
+<code>
+Message file sp1<lang>.msb not found
+SP2-0750 :You may need to set ORACLE_HOME to your Oracle software directory
+</code>
 so...
-<code>28@@</code>
+<code>
-=====ORA-2002: error while writing to audit trail=====
+export ORACLE_SID=KITRYD
+ORAENV_ASK=NO
+. oraenv
+[[ "`uname`" == CYGWIN* ]] && ORACLE_HOME=`cygpath -w ${ORACLE_HOME}`
+</code>
+==== ORA-2002: error while writing to audit trail ====
 Check the space left in the audit tablespace with:
-<code>29@@</code>
+<code>
-If plenty of space, it can be due to Oracle clearing out the recyclebin at the same time.<br />
+select sum(bytes)/1024/1024 mb from dba_free_space where tablespace_name ='AUDIT_DT';
+</code>
+If plenty of space, it can be due to Oracle clearing out the recyclebin at the same time.\\
 If not, extend the datafile...
-<code>30@@</code>
+<code>
+select * from dba_data_files where tablespace_name ='AUDIT_DT';
+alter database datafile 6 resize 30G;
+</code>
 If that is not possible, clean out some audit data or add a datafile.
-=====ORA-00449: background process 'CKPT' unexpectedly terminated with error 7446=====
+==== ORA-00449: background process 'CKPT' unexpectedly terminated with error 7446 ====
-<code>31@@</code>
+<code>
+SQL> startup nomount pfile="/oracle/TRSCRP1/admin/initTRSCRP1.ora"
+ORA-00449: background process 'CKPT' unexpectedly terminated with error 7446
+ORA-07446: sdnfy: bad value // for parameter .
+SQL> exit
+</code>
 **Solution**
-<code>32@@</code>
+<code>
+SQL> mkdir /oracle/TRSCRP1/admin/bdump /oracle/TRSCRP1/admin/cdump
+</code>
-=====ORA-00845: MEMORY_TARGET not supported on this system=====
+==== ORA-00845: MEMORY_TARGET not supported on this system ====
-  * Reference: [[http://arjudba.blogspot.be/2009/01/ora-00845-memorytarget-not-supported-on.html|arjudba.blogspot.be]]
+  *  Reference: [[http://arjudba.blogspot.be/2009/01/ora-00845-memorytarget-not-supported-on.html|arjudba.blogspot.be]]
 **Problem Description**
-<code>33@@</code>
+<code>
+SQL> STARTUP
+ORA-00845: MEMORY_TARGET not supported on this system
+</code>
 **Cause**
-  * Starting from Oracle 11g the automatic memory management feature is now defined with parameter MEMORY_TARGET and MEMMORY_MAX_TARGET.
+  *  Starting from Oracle 11g the automatic memory management feature is now defined with parameter MEMORY_TARGET and MEMMORY_MAX_TARGET.
-  * On linux file system the shared memory need to be mounted on /dev/shm directory on the operating system.
+  *  On linux file system the shared memory need to be mounted on /dev/shm directory on the operating system.
-  * And the size of /dev/shm needs to be greater than MEMORY_TARGET or MEMMORY_MAX_TARGET.
+  *  And the size of /dev/shm needs to be greater than MEMORY_TARGET or MEMMORY_MAX_TARGET.
-  * The AMM (Automatic Memory Management) now in 11g manages both SGA and PGA together by MMAN process.
+  *  The AMM (Automatic Memory Management) now in 11g manages both SGA and PGA together by MMAN process.
-  * The MEMORY_TARGET parameter in 11g comes for (SGA_TARGET+PGA_AGGREGATE_TARGET) which was in 10g.
+  *  The MEMORY_TARGET parameter in 11g comes for (SGA_TARGET+PGA_AGGREGATE_TARGET) which was in 10g.
-  * And MEMORY_MAX_TARGET parameter in 11g comes instead of SGA_MAX_TARGET parameter which was in 10g.
+  *  And MEMORY_MAX_TARGET parameter in 11g comes instead of SGA_MAX_TARGET parameter which was in 10g.
-  * The ORA-00845:can arises for the following two reasons on linux system.
+  *  The ORA-00845:can arises for the following two reasons on linux system.
 )If the shared memory which is mapped to /dev/shm directory is less than the size of MEMORY_TARGET or MEMORY_MAX_TARGET or
 )If the shared memory is not mapped to /dev/shm directory.
 **Solution**
-<code>34@@</code>
+<code>
+Make sure /dev/shm is properly mounted. You can see it by,
+  - df -h or #df -k command.
+The output should be similar to:
-=====ORA-24247: network access denied by access control list (ACL)=====
+$ df -k
-<code>35@@</code>
+Filesystem            Size  Used Avail Use% Mounted on
+...
+shmfs                 1G    512M 512M  50% /dev/shm
+We see here for /dev/shm we have assigned 1G memory. Now if you set MEMORY_TARGET more than 1G then above ORA-845 will arise. For example if you have MEMORY_TARGET or MEMORY_MAX_TARGET set to 12G then you can mount shared memory to 13g like below.
+As a root user,
+  -  mount -t tmpfs shmfs -o size=13g /dev/shm
+In order to make the settings persistence so that it will affect after restarting machine add an entry in /etc/fstab similar to the following:
+shmfs /dev/shm tmpfs size=13g 0
+</code>
+==== ORA-23515: materialized views and/or their indices exist in the tablespace ====
+While trying to drop a tablespace, this error is shown.\\
+Problem:\\
+As the message suggests, you cannot drop a tablespace while it contains materialised views or their dependent objects.\\
+Solution:\\
+Find view dependencies, drop them. Drop the views. Drop the tablespace.
+<code>
+set pages 100 lines 1000
+col owner      format a15;
+col table_name format a45;
+select t.owner
+,      t.table_name
+from   dba_tables  t
+,      dba_indexes i
+where  i.index_type      = 'DOMAIN'
+and    t.owner           = i.table_owner
+and    t.table_name      = i.table_name
+and    t.tablespace_name = upper('&tablespace_name')
+/
+</code>
+Materialised view indexes
+<code>
+select o.owner
+,      o.object_name index_name
+,      o.object_type object_type
+,      sq.mv_owner   mv_owner
+,      sq.mv_name    mv_name
+,      sq.tablespace mv_tablespace
+from   dba_objects o
+,      (
+       select i.obj#
+       ,      s.sowner mv_owner
+       ,      s.tname  mv_name
+       ,      p.name   tablespace
+       from   sys.obj$  o
+       ,      sys.tab$  t
+       ,      sys.ind$  i
+       ,      sys.user$ u
+       ,      sys.snap$ s
+       ,      sys.ts$   p
+       where  i.ts#    = p.ts#
+       and    p.name   = '&TABLESPACE'
+       and    i.bo#    = t.obj#
+       and    t.obj#   = o.obj#
+       and    o.owner# = u.user#
+       and    u.name   = s.sowner
+       and    o.name   = s.tname
+       ) sq
+where  sq.obj# = o.object_id
+order  by 1,2,3
+/
+</code>
+Materialised views
+<code>
+set pages 100 lines 1000
+col mv_owner    format a15;
+col mv_name     format a45;
+col tablespace  format a45;
+select s.sowner mv_owner
+,      s.tname  mv_name
+,      p.name   tablespace
+from   sys.obj$ o
+,      sys.tab$ t
+,      sys.user$ u
+,      sys.snap$ s
+,      sys.ts$ p
+where  t.ts#    = p.ts#
+and    p.name   = upper('&TABLESPACE')
+and    t.obj#   = o.obj#
+and    o.owner# = u.user#
+and    u.name   = s.sowner
+and    o.name   = s.tname
+order  by 1,2
+/
+</code>
+==== ORA-02429: cannot drop index used for enforcement of unique/primary key ====
+Atempting to drop a tablespace but got this error message. It means that some tables have constraints used to enforce uniqueness. These have to be found and dropped/diabled before the table(space) can be removed.\\
+Of course the owning schema could also just be dropped with cascade option if this is part of the users request.
+<code>
+select 'ALTER TABLE '||owner||'.'||table_name||' DISABLE CONSTRAINT '|| constraint_name||' cascade;'
+from   dba_constraints
+where  (index_owner,index_name) in (
+       select owner,index_name
+       from   dba_indexes
+       where  tablespace_name = upper('&tablespace_name')
+       )
+</code>
+<code>
+'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||';'
+---------------------------------------------------------------------------------
+ALTER TABLE OWBSYS.CMPFCOSTORAGE DISABLE CONSTRAINT IDX_FCOELEMENTID_VER;
+ALTER TABLE OWBSYS.CMPSCOCFGSTORAGE DISABLE CONSTRAINT IDX_SCOCFGELEMENTID_VER;
+ALTER TABLE OWBSYS.CMPSCOSTORAGE DISABLE CONSTRAINT IDX_SCOELEMENTID_VER;
+ALTER TABLE OWBSYS.CMPSCOMAPSTORAGE DISABLE CONSTRAINT IDX_SCOMAPELEMENTID_VER;
+ALTER TABLE OWBSYS.CMPSCOPRPSTORAGE DISABLE CONSTRAINT IDX_SCOPRPELEMENTID_VER;
+ALTER TABLE OWBSYS.CMPSYSSTORAGE DISABLE CONSTRAINT IDX_SYSELEMENTID_VER;
+rows selected.
+</code>
+==== ORA-24247: network access denied by access control list (ACL) ====
+<code>
+Cause: No access control list (ACL) has been assigned to the target host or the privilege necessary to access the target host has not been granted to the user in the access control list.
+Action: Ensure that an access control list (ACL) has been assigned to the target host and the privilege necessary to access the target host has been granted to the user.
+</code>
 Your application will encounter an ORA-24247 error if it relies on one of the network packages and no proper ACL has been created. For the use of the following packages it is mandatory to have an ACL for the application user in place in 11g:
-*UTL_TCP
+  * UTL_TCP
-*UTL_SMTP
+  * UTL_SMTP
-*UTL_MAIL
+  * UTL_MAIL
-*UTL_HTTP
+  * UTL_HTTP
-*UTL_INADDR
+  * UTL_INADDR
-Here is a very good explanation of how to setup the necessary ACLs: [[http://blog.whitehorses.nl/2010/03/17/oracle-11g-access-control-list-and-ora-24247/ whitehorses.nl]]
+Here is a very good explanation of how to setup the necessary ACLs: [[http://blog.whitehorses.nl/2010/03/17/oracle-11g-access-control-list-and-ora-24247/|whitehorses.nl]]
+==== ORA-00600: internal error code, arguments: [17114] ====
+<code>
+ORA-00603: ORACLE server session terminated by fatal error
+ORA-24557: error 600 encountered while handling error 600; exiting server process
+ORA-00600
+...
+********** Internal heap ERROR 17114 addr=1109c20f0 *********
+...
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+Error/Stack/SQL/Version from file "lbk5_p007_58852410_i509563.trc"
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+Error: ORA-00600 [17114] [0x1109C20F0]
+Error Stack: ORA-600[17114]/ORA-603/ORA-24557/ORA-600
+Main Stack:
+kgherror <- kghrcdepth <- kghrcdepth <- kghrcappl <- ksmdpg <- opidcl <- opidrv <- sou2o
+<- opimai_real <- ssthrdmain <- main
+</code>
+Opened SR 3-24837203691 at Oracle. It seems to be bug 18909196 related to the xml version of the alertlog (X$DBGALERTEXT).
+Either apply patch 18909196 or "Remove (i.e. delete) the xml-based alert log within <ADR_HOME>/alert/log.xml (and log_*.xml).  Or manually locate the specific alert log records with problem keys exceeding 64 characters and delete only those."
+==== ORA-00600: internal error code, arguments: [kewrsp_split_partition_2], [8], [2982267510], [183212], [], [], [], [], [], [], [], [] ====
+Reference [https://support.oracle.com/epmos/faces/DocumentDisplay?id=17042658.8|Bug 17042658 - ORA-600 [kewrsp_split_partition_2] during AWR purge (Doc ID 17042658.8)]
-=====ORA-00600: internal error code, arguments: [[qmx:|no ref]]=====
+==== ORA-00600: internal error code, arguments: [[qmx: no ref]] ====
 Search "Master Note for Oracle XML Database (XDB) Install / Deinstall (Doc ID 1292089.1)" for more info.
-====Problem:====
+=== Problem: ===
-Sometimes one or more of the following errors can be encountered when installing or upgrading XDB, configuring APEX, running an Export, or selecting from xdb.xdb$resource or sys.dba_network_acls:<br />
+Sometimes one or more of the following errors can be encountered when installing or upgrading XDB, configuring APEX, running an Export, or selecting from xdb.xdb$resource or sys.dba_network_acls:\\
-  * ORA-31159: XML DB is in an invalid state
+  *  ORA-31159: XML DB is in an invalid state
-  * ORA-00600: internal error code, arguments: [[unable|to load XDB library]]
+  *  ORA-00600: internal error code, arguments: [[unable to load XDB library]]
-  * ORA-00600: internal error code, arguments: [[qmx:|no ref]]
+  *  ORA-00600: internal error code, arguments: [[qmx: no ref]]
-  * ORA-00600: internal error code, arguments: [[qmtGetColumnInfo1]]
+  *  ORA-00600: internal error code, arguments: [[qmtGetColumnInfo1]]
-  * ORA-00600: internal error code, arguments: [[qmtb_init_len]]
+  *  ORA-00600: internal error code, arguments: [[qmtb_init_len]]
-  * ORA-00600: internal error code, arguments: [[qmtGetBaseType]]
+  *  ORA-00600: internal error code, arguments: [[qmtGetBaseType]]
-  * ORA-00600: internal error code, arguments: [[psdnop-1]], [[600]]
+  *  ORA-00600: internal error code, arguments: [[psdnop-1]], [[600]]
-  * ORA-00600: internal error code, arguments: [[qmtInit1]]
+  *  ORA-00600: internal error code, arguments: [[qmtInit1]]
-  * ORA-07445: exception encountered: core dump [[_memcpy()+224]] [SIGSEGV] [[Address|not mapped to object]]
+  *  ORA-07445: exception encountered: core dump [[_memcpy()+224]] [[SIGSEGV]] [[Address not mapped to object]]
-  * ORA-19051 Cannot Use Fast Path Insert For This XMLType Table
+  *  ORA-19051 Cannot Use Fast Path Insert For This XMLType Table
-  * ORA-31011: XML parsing failed
+  *  ORA-31011: XML parsing failed
-Errors of this sort generally occur when the init routines for the internal XDB functions are run in an invalid environment causing memory corruption.<br />
+Errors of this sort generally occur when the init routines for the internal XDB functions are run in an invalid environment causing memory corruption.\\
-<br />
-This can happen if the database was ever started with the LD_LIBRARY_PATH (LIBPATH for AIX or SHLIB_PATH for HP) pointing to the wrong $ORACLE_HOME/lib directory rather than to the correct location for the instance.<br />
+This can happen if the database was ever started with the LD_LIBRARY_PATH (LIBPATH for AIX or SHLIB_PATH for HP) pointing to the wrong $ORACLE_HOME/lib directory rather than to the correct location for the instance.\\
 The LD_LIBRARY_PATH/LIBPATH/SHLIB_PATH environment variable is used to resolve the location of the shared library "libxdb.so (libxdb.sl on HP)".
-====Solution:====
+=== Solution: ===
-  * Stop Listener
+  *  Stop Listener
-  * Stop database
+  *  Stop database
-  * Ensure $ORACLE_HOME/lib is at the start of LD_LIBRARY_PATH / LIBPATH / SHLIB_PATH
+  *  Ensure $ORACLE_HOME/lib is at the start of LD_LIBRARY_PATH / LIBPATH / SHLIB_PATH
-  * If AIX, run /usr/sbin/slibclean as root
+  *  If AIX, run /usr/sbin/slibclean as root
-  * Start database
+  *  Start database
-  * Start listener
+  *  Start listener
-=====ORA-00201: control file version nn.nn.nn.nn incompatible with ORACLE version=====
+==== ORA-00201: control file version nn.nn.nn.nn incompatible with ORACLE version ====
-Trying to restore a database after complete failure<br />
+Trying to restore a database after complete failure\\
-first step is to startup the database in nomount mode and restore the controlfile - succeeded.<br />
+first step is to startup the database in nomount mode and restore the controlfile - succeeded.\\
 second step, startup the database in mount mode ready for database restore...
-<code>36@@</code>
+<code>
-The problem was there was no compatible parameter in the simple init file I used to start up the instance.<br />
+SQL*Plus: Release 10.2.0.3.0 - Production on Wed Aug 19 18:16:47 2015
+Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
+Connected to:
+Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
+With the Partitioning, OLAP and Data Mining options
+SYS@CODWHP1> startup mount
+ORACLE instance started.
+Total System Global Area  260046848 bytes
+Fixed Size                  2029296 bytes
+Variable Size             113248528 bytes
+Database Buffers          134217728 bytes
+Redo Buffers               10551296 bytes
+ORA-00201: control file version 10.2.0.0.0 incompatible with ORACLE version 10.2.0.3.0
+ORA-00202: control file: '/oracle/CODWHP1/product/10203/dbs/cntrlCODWHP1.dbf'
+</code>
+The problem was there was no compatible parameter in the simple init file I used to start up the instance.\\
 Adding compatible parameter to init file to be same version (10.2.0.3) allowed the db to mount.
-=====TNS-00525: Insufficient privilege for operation=====
-<code>37@@</code>
+==== ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_131081 ====
-This would suggest the user attempting to start the listener has no access to either the Oracle binaries or the TNS listener/tnsnames files.<br />
+Following found in trace file from a autotask job run
-But... checkout the /tmp/.oracle directory :-)<br />
+<code>
-That hidden gem shows who started the listener last time. Delete the relevant entry and try again!<br />
+ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_131081"
+<error barrier> at 0xfffffffffff3758 placed jslv.c@1659
+ORA-20001: Statistics Advisor: Invalid task name for the current user
+ORA-06512: at "SYS.DBMS_STATS", line 49540
+ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 881
+ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 21631
+ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 23763
+ORA-06512: at "SYS.DBMS_STATS", line 49528
+</code>
+See [[https://dbtut.com/index.php/2019/07/16/ora-20001-statistics-advisor-invalid-task-name-for-the-current-user/|ORA-20001: Statistics Advisor: Invalid task name for the current user]]
+<code>
+set lines 1000
+col name       for a40
+col owner_name for a10
+select name
+,      ctime
+,      how_created
+,      owner_name
+from   sys.wri$_adv_tasks
+where  name in ('AUTO_STATS_ADVISOR_TASK', 'INDIVIDUAL_STATS_ADVISOR_TASK')
+/
+</code>
+If records are retrieved but not owned by SYS or not created by CMD then drop them first...
+<code>
+DECLARE
+v_tname VARCHAR2(32767);
+BEGIN
+v_tname := 'AUTO_STATS_ADVISOR_TASK';
+DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
+END;
+/
+DECLARE
+v_tname VARCHAR2(32767);
+BEGIN
+v_tname := 'INDIVIDUAL_STATS_ADVISOR_TASK';
+DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
+END;
+/
+</code>
+If no rows retrieved or they have just been dropped, now recreate them
+<code>
+exec dbms_stats.init_package();
+</code>
+==== TNS-00525: Insufficient privilege for operation ====
+<code>
+lsnrctl start LISTENER_UCLID55V
+LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 26-MAR-2015 13:36:14
+Copyright (c) 1991, 2011, Oracle.  All rights reserved.
+Starting /oracle/product/11.2.0.3/bin/tnslsnr: please wait...
+TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
+System parameter file is /oracle/product/11.2.0.3/network/admin/listener.ora
+Log messages written to /oracle/product/diag/tnslsnr/solax082/listener_uclid55v/alert/log.xml
+Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=150.251.114.238)(PORT=1525)))
+Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1525)))
+TNS-12555: TNS:permission denied
+ TNS-12560: TNS:protocol adapter error
+  TNS-00525: Insufficient privilege for operation
+   IBM/AIX RISC System/6000 Error: 1: Not owner
+Listener failed to start. See the error message(s) above...
+</code>
+This would suggest the user attempting to start the listener has no access to either the Oracle binaries or the TNS listener/tnsnames files.\\
+But... checkout the /tmp/.oracle directory :-)\\
+That hidden gem shows who started the listener last time. Delete the relevant entry and try again!\\
 To start from a clean sheet, stop all listeners, delete this directory and restart the listeners.
-=====ORA-12003: materialized view or zonemap "KPK"."EVD01_NEW" does not exist=====
+==== ORA-12537: TNS:connection closed ====
-Here's one you shouldn't see very often.<br />
+<code>
+-NOV-2019 10:37:53 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=devopse)(CID=(PROGRAM=sqlplus@hn5118)(HOST=hn5118)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.8.90.27)(PORT=35278)) * establish * devopse * 12518
+TNS-12518: TNS:listener could not hand off client connection
+ TNS-12547: TNS:lost contact
+  TNS-12560: TNS:protocol adapter error
+   TNS-00517: Lost contact
+    IBM/AIX RISC System/6000 Error: 32: Broken pipe
+</code>
+Not the usual problems... listener log full, oracle executable without setuid bit set.\\
+This turned out to be not very relevant to the error message but...\\
+tnsnames.ora looked like this
+<code>
+DEVOPSE,DEVOPSE.WORLD =
+  (DESCRIPTION =
+    (ADDRESS = (PROTOCOL = TCP)(HOST = hn511)(PORT = 3531))
+    (CONNECT_DATA =
+      (SERVER = DEDICATED)
+      (SERVICE_NAME = devopse)
+    )
+  )
+</code>
+and database parameters looked like this
+<code>
+SQL> show parameter service
+NAME                                 TYPE        VALUE
+------------------------------------ ----------- ------------------------------
+service_names                        string      devopse
+SQL> show parameter domain
+NAME                                 TYPE        VALUE
+------------------------------------ ----------- ------------------------------
+db_domain                            string
+</code>
+The database registers dynamically so there is nothing in the listener.ora other than the listener definition itself.\\
+This should work! I have no idea why it doesn't. But, as all the other databases use .world in their connections, I modified this setup to match:
+<code>
+SQL> alter system set db_domain='world' scope=spfile;
+System altered.
+SQL>
+</code>
+Restart the database.
+Now look at what the parameters look like
+<code>
+SQL> show parameter service
+NAME                                 TYPE        VALUE
+------------------------------------ ----------- ------------------------------
+service_names                        string      devopse.world
+SQL> show parameter domain
+NAME                                 TYPE        VALUE
+------------------------------------ ----------- ------------------------------
+db_domain                            string      world
+</code>
+Note the service name changed to have the domain tagged on to it so we have to modify the tnsnames.ora service name otherwise we'll get ORA-12514.
+so now the tnsnames.ora looks like this
+<code>
+DEVOPSE,DEVOPSE.WORLD =
+  (DESCRIPTION =
+    (ADDRESS = (PROTOCOL = TCP)(HOST = hn511)(PORT = 3531))
+    (CONNECT_DATA =
+      (SERVER = DEDICATED)
+      (SERVICE_NAME = devopse.world)
+    )
+  )
+</code>
+Strangely, it now all works (either connecting to sqlplus user/pass@devopse or sqlplus user/[email protected]).
+==== minact-scn: got error during useg scan e:12751 usn:5 ====
+and
+<code>
+minact-scn: useg scan erroring out with error e:12751
+Sat Aug 26 13:40:55 2023
+Incremental checkpoint up to RBA [0x4c28.f4b86.0], current log tail at RBA [0x4c28.116367.0]
+Sat Aug 26 13:48:53 2023
+Non critical error ORA-48913 caught while writing to trace file "/cln/tst/ora_ebstt1/db12cR1/tech_st/12.1.0.2/admin/EBSTT1_hn5122/diag/rdbms/ebstt1/EBSTT1/trace/EBSTT1_lg00_61276276.trc"
+Error message: ORA-48913: Writing into trace file failed, file size limit [10485760] reached
+Writing to the above trace file is disabled for now on...
+Sat Aug 26 13:49:29 2023
+minact-scn: got error during useg scan e:12751 usn:5
+minact-scn: useg scan erroring out with error e:12751
+Sat Aug 26 13:54:36 2023
+Suspending MMON action 'undo usage' for 82800 seconds
+Sat Aug 26 13:59:36 2023
+minact-scn: got error during useg scan e:12751 usn:5
+minact-scn: useg scan erroring out with error e:12751
+Suspending MMON action 'Block Cleanout Optim, Undo Segment Scan' for 82800 seconds
+</code>
+in alertlog.
+The archiving has stopped. Jobs are backing up in Commvault. Now the database has stopped responding.
+Strangely, if you do a
+<code>
+sho parameter log_archive_dest_state_1
+</code>
+you see
+<code>
+NAME                                 TYPE        VALUE
+------------------------------------ ----------- ------------------------------
+log_archive_dest_1                   string      location=/cln/tst/ora_ebstt
+/db/apps_st/data/archive mand
+                                                 atory max_failure=1 alternate=
+                                                 log_archive_dest_2
+log_archive_dest_state_1             string      ENABLE
+</code>
+but querying v$archive_dest shows
+<code>
+set head off
+select*from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_1';
+
+LOG_ARCHIVE_DEST_1
+DEFERRED  MANDATORY SYSTEM  PRIMARY          ARCH       ACTIVE
+/cln/tst/ora_ebstt1/db/apps_st/data/archive
+         300          0               1           0 ARCH       YES
+-JUL-23 23:48:43               17391          0             1           1
+ORA-19502: write error on file "", block number  (block size=)
+</code>
+Resetting log_archive_dest_1 to itself does not help. But setting the state to defer and back to enable does the trick.
+<code>
+SQL> alter system set log_archive_dest_state_1='defer';
+System altered.
+SQL> alter system set log_archive_dest_state_1='enable';
+System altered.
+SQL> select*from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_1';
+
+LOG_ARCHIVE_DEST_1
+VALID     MANDATORY SYSTEM  PRIMARY          ARCH       ACTIVE
+/cln/tst/ora_ebstt1/db/apps_st/data/archive
+         300          0               1           0 ARCH       YES
+          0             0           0
+</code>
+If the jobs start running, all is good. Otherwise a db restart fixes the issue.
+See [[https://support.oracle.com/epmos/faces/DocumentDisplay?id=1478691.1|Doc ID 1478691.1]] for reasons why this might happen (generally performance issues).
+==== ORA-01031: insufficient privileges ====
+Scratching my head over this seemingly simple looking error.\\
+This was the error seen on the screen
+<code>
+Connected to:
+Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
+Version 18.4.0.0.0
+SQL> alter trigger cre_dba.trg_con_users disable;
+alter trigger cre_dba.trg_con_users disable
+              *
+ERROR at line 1:
+ORA-01031: insufficient privileges
+SQL>
+</code>
+This was in te alertlog
+<code>
+-03-21T18:24:33.352191+01:00
+SERVER COMPONENT id=UTLRP_BGN: timestamp=2019-03-21 18:24:33
+-03-21T18:28:08.081207+01:00
+Errors in file /cln/tst/ora_bin1/app/oracle/diag/rdbms/upg/upg/trace/upg_ora_46728206.trc:
+ORA-04045: errors during recompilation/revalidation of CRE_DBA.TRG_CON_USERS
+ORA-01031: insufficient privileges
+</code>
+The clue was when looking at the content of the trigger type. It was a database logon trigger.\\
+It seems a special privilege is required!
+<code>
+SQL> grant administer database trigger to cre_dba;
+Grant succeeded.
+SQL>
+</code>
+<code>
+Connected to:
+Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
+Version 18.4.0.0.0
+SQL> alter trigger cre_dba.trg_con_users disable;
+Trigger altered.
+SQL>
+</code>
+==== RMAN-05624: data file name not found in the repository for data file number=80 ====
+Getting these errors using a targetless connection (only auxiliary and repository). And doing this because we don't want the transportable checks to be done!
+<code>
+RMAN-03002: failure of Duplicate Db command at 08-MAR-24
+RMAN-05501: aborting duplication of target database
+RMAN-05624: data file name not found in the repository for data file number=80
+RMAN-05624: data file name not found in the repository for data file number=801
+RMAN-05624: data file name not found in the repository for data file number=802
+RMAN-05624: data file name not found in the repository for data file number=803
+</code>
+The reason seems to be a corruption of the database in the repository.
+<code>
+unregister database;
+register database;
+</code>
+and the problem is fixed.
+==== ORA-27044: unable to write the header block of file ====
+Multiple errors in alertlog related to redo and archiving.
+<code>
+-05-09T00:10:12.708010+02:00
+Errors in file /cln/acc/ora_bin/app/oracle/diag/rdbms/credoaa2/credoaa2/trace/credoaa2_arc3_51839560.trc:
+ORA-19504: failed to create file "/cln/acc/ora_data/archivelog/credoaa2/log_1_1165_1167383538.arc"
+ORA-27044: unable to write the header block of file
+IBM AIX RISC System/6000 Error: 89: Invalid file system control data detected
+Additional information: 3
+Additional information: 4294967295
+Additional information: 1
+ARC3 (PID:51839560): Error 19504 Creating archive log file to '/cln/acc/ora_data/archivelog/credoaa2/log_1_1165_1167383538.arc'
+ARC3 (PID:51839560): Stuck archiver: insufficient local LADs
+ARC3 (PID:51839560): Stuck archiver condition declared
+</code>
+It seems the filesystem (or several of them) is (are) corrupt.\\
+Operating system admins need to be involved to fsck the relevant filesystems.
+==== ORA-19563: Inspect Datafile Copy: SCN number validation unsuccessful header validation failed for file <filename> ====
+Following errors occured after restoring all the datafiles, switching them and then shutting down to rename the database in the controlfile
+<code>
+contents of Memory Script:
+{
+   sql clone "alter system set  db_name =
+ ''CRELTT1'' comment=
+ ''Reset to original value by RMAN'' scope=spfile";
+   sql clone "alter system reset  db_unique_name scope=spfile";
+   shutdown clone immediate;
+}
+executing Memory Script
+sql statement: alter system set  db_name =  ''CRELTT1'' comment= ''Reset to original value by RMAN'' scope=spfile
+sql statement: alter system reset  db_unique_name scope=spfile
+Oracle instance shut down
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-03002: failure of Duplicate Db command at 07/21/2023 13:39:07
+RMAN-05501: aborting duplication of target database
+RMAN-03015: error occurred in stored script Memory Script
+RMAN-06136: Oracle error from auxiliary database: ORA-19563: Inspect Datafile Copy: SCN number validation unsuccessful header validation failed for file /cln/tst/ora_data/creltt1/ts_thaler_data_361.dbf
+Recovery Manager complete.
+</code>
+The message is misleading at first but after 'a while' the penny dropped.\\
+There were two files with the same name in different directories on the source system. Unusual, but not an issue in itself. But this duplicate had a db_filename_convert parameter which caused all the files to be collected into one big filesystem.\\
+So the controlfile saw two filenames the same (with different file numbers) but only one file on the destination filesystem (the other being overwritten).\\
+Tried and failed to continue opening the database (to restore the other datafile) so renamed one of the duplicate filenames on the source system and reran the duplicate.
+UPDATE:
+Happened again but this time there were 19 duplicate datafiles so not worth the energy of finding them and renaming them. The answer is to use %U in the set newname command. It makes a mess of the filenames but at least they are all guaranteed to be unique!
+<code>
+set echo on
+connect auxiliary /;
+connect catalog rman/****@rmancat;
+run
+{
+    allocate auxiliary channel t0 type sbt_tape parms 'SBT_LIBRARY=/opt/commvault/Base/libobk.a(shr.o)' ;
+    allocate auxiliary channel t1 type sbt_tape parms 'SBT_LIBRARY=/opt/commvault/Base/libobk.a(shr.o)' ;
+    allocate auxiliary channel t2 type sbt_tape parms 'SBT_LIBRARY=/opt/commvault/Base/libobk.a(shr.o)' ;
+    allocate auxiliary channel t3 type sbt_tape parms 'SBT_LIBRARY=/opt/commvault/Base/libobk.a(shr.o)' ;
+    set until time '2024-01-05 22:29:14';
+    set newname for database to '/cln/tst/ora_data/creltt2/%U';
+    duplicate database clne dbid 3045137910 to creltt2
+    nofilenamecheck skip tablespace TS_THALER_IOT_OLD,TS_THALER_CU,TS_THALER_BACKUP,TS_THALER_PART_OLD
+    ;
+}
+</code>
+==== RMAN-05548: The set of duplicated tablespaces is not self-contained ====
+<code>
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-03002: failure of Duplicate Db command at 01/16/2024 10:53:48
+RMAN-05501: aborting duplication of target database
+RMAN-05548: The set of duplicated tablespaces is not self-contained
+</code>
+This error means that there are probably partitioned tables with partitions in different tablespaces (or other such things). Depending on the version of the database, there may be violation messages posted higher up in the logfile...
+<code>
+     Violation: ORA-39921: Default Partition (Table) Tablespace TS_THALER_DATA for PGD29 not contained in transportable set.
+     Violation: ORA-39901: Partitioned table EPK.PGD33 is partially contained in the transportable set.
+     Violation: ORA-39910: Partitioned Global index EPK.PGD33_1 in tablespace TS_THALER_INDEX points to partition P10 of table EPK.PGD33 in tablespace TS_THALER_PART_OLD outside of transportable set.
+     Violation: ORA-39911: Index EPK.AGD11_4 in tablespace TS_THALER_INDEX points to partition P0413 of table EPK.AGD11 in tablespace TS_THALER_PART_OLD outside of transportable set.
+</code>
+This is entirely legal thing to do in the database so RMAN should handle it, but it doesn't. The workaround is to perform the duplicate without connecting to the target. Just use auxiliary and catalog connection.
+==== RMAN-06025: no backup of log thread 1 seq 2668 lowscn 1079975830 found to restore ====
+Duplicate failed during recovery. Seemingly cannot find an archived log.
+Looking at the sequence# shows it is a lot older than it should be (very likely already aged out of the controlfile and the catalog).
+According to [[https://support.oracle.com/epmos/faces/DocumentDisplay?id=238422.1|Doc ID 238422.1]], it is due to a previously failed recovery that was completed using SQL instead of RMAN. From the rman catalog database, test with:
+<code>
+set lines 1000 pages 100
+col name for a50
+set numwid 20 lines 1000
+col name for a50
+select name, resetlogs_change#, recid, stamp, sequence#, archived, first_change#, first_time, next_change#, next_time, status
+from   v$archived_log
+where  &&recovery_scn between first_change# and next_change#
+/
+</code>
+If there is a record where the name is not null, the archived is 'N' and the next_scn is extremely high, then this is the case.
+=== Solution ===
+From the rman catalog database...
+Find dbinc_key of the database using
+<code>
+select * from rc_database
+/
+</code>
+then delete the offending records
+<code>
+delete
+from   rman.al
+where  1=1
+and    archived  = 'N'
+and    name      is not null
+and    dbinc_key = <dbinc_key of database>  # do we want this? Maybe we want these gone for all databases!
+/
+</code>
+and rerun the duplicate.
+The chances are that this corrupt record exists in v$archived_log (so, the controlfile) This cannot be fixed without rebuilding the controlfile.
+==== RMAN-06136: Oracle error from auxiliary database: ORA-19715: invalid format b for generated name ====
+When duplicating a database with "set newname for database to ...", cloning fails after restoring the controlfiles with:
+Also received
+====ORA-19838: cannot use this control file to mount or open database====
+Led to the same fix
+<code>
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-03002: failure of Duplicate Db command at 07/21/2023 11:21:29
+RMAN-05501: aborting duplication of target database
+RMAN-06136: Oracle error from auxiliary database: ORA-19715: invalid format b for generated name
+ORA-27302: failure occurred at: slgpn
+</code>
+Doc ID 2771480.1 explains the issue but does not explain how the error occurred.\\
+I suspect something went wrong with the restore of the controlfiles during a previous attempt and the source db_name got an extra entry in the catalog using the destination db_unique_name.\\
+=== Solution: ===
+Go to the source db server, set the environment of the source database and connect to the catalog
+<code>
+RMAN> list db_unique_name of database;
+List of Databases
+DB Key  DB Name  DB ID            Database Role    Db_unique_name
+------- ------- ----------------- ---------------  ------------------
+2012947 CRELTST  3803052590       PRIMARY          CRELTST
+2012947 CRELTST  3803052590       STANDBY          CRELTT1
+</code>
+An extra entry has been inserted (as a standby)
+<code>
+RMAN> unregister db_unique_name creltt1;
+database db_unique_name is "creltt1", db_name is "CRELTST" and DBID is 3803052590
+Want to unregister the database with target db_unique_name (enter YES or NO)? yes
+database with db_unique_name creltt1 unregistered from the recovery catalog
+RMAN> list db_unique_name of database;
+List of Databases
+DB Key  DB Name  DB ID            Database Role    Db_unique_name
+------- ------- ----------------- ---------------  ------------------
+2012947 CRELTST  3803052590       PRIMARY          CRELTST
+RMAN> exit
+Recovery Manager complete.
+</code>
+Rerun the duplicate and it works
+==== RMAN-06564: must use the TO clause when the instance is started with SPFILE ====
+From the Commvault console, running a restore...
+<code>
+Rman Script:
+[run {
+allocate channel ch1 type 'sbt_tape'
+PARMS="SBT_LIBRARY=/opt/commvault/Base64/libobk.a(shr.o), BLKSIZE=1048576 ENV=(CV_mmsApiVsn=2,CV_channelPar=ch1,ThreadCommandLine= -cn CI00031899-hn481 -cs 212.113.81.130)"
+TRACE 0;
+send " -jm 131082 -a 2:-1 -cl 18194 -ins 4351 -at 22 -j 73218120 -jt 73218120:2:1:0:61384 -rcp 0 -ms 2 -p 1 -df";
+ restore spfile ;
+ startup force nomount;
+}
+exit;
+]
+Rman Log:[
+Recovery Manager: Release 12.1.0.2.0 - Production on Tue Dec 28 14:58:37 2021
+Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
+RMAN>
+connected to recovery catalog database
+recovery catalog schema release 19.13.00.00. is newer than RMAN release
+RMAN>
+connected to target database: EBST (not mounted)
+RMAN> 2> 3> 4> 5> 6> 7> 8>
+allocated channel: ch1
+channel ch1: SID=406 device type=SBT_TAPE
+channel ch1: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
+sent command to channel: ch1
+Starting restore at Dec 28 2021 14:58:39
+released channel: ch1
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-03002: failure of restore command at 12/28/2021 14:58:39
+RMAN-06564: must use the TO clause when the instance is started with SPFILE
+RMAN>
+Recovery Manager complete.
+]
+</code>
+Solution: Make sure there is an init<SID>.ora file and delete the spfile.
+==== RMAN-06004: ORACLE error from recovery catalog database: RMAN-20005: target database name is ambiguous ====
+Again from the Commvault console but could have been the RMAN command line, the problem here is that the database was registered twice with different DBIDs
+<code>
+Rman Script:
+[run {
+allocate channel ch1 type 'sbt_tape'
+PARMS="SBT_LIBRARY=/opt/commvault/Base64/libobk.a(shr.o), BLKSIZE=1048576 ENV=(CV_mmsApiVsn=2,CV_channelPar=ch1,ThreadCommandLine= -cn CI00031899-hn481 -cs 212.113.81.130)"
+TRACE 0;
+send " -jm 131082 -a 2:-1 -cl 18194 -ins 4351 -at 22 -j 73218120 -jt 73218120:2:2:0:61384 -rcp 0 -ms 2 -p 1 -df";
+ restore spfile ;
+ startup force nomount;
+}
+exit;
+]
+Rman Log:[
+Recovery Manager: Release 12.1.0.2.0 - Production on Tue Dec 28 15:07:18 2021
+Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
+RMAN>
+connected to recovery catalog database
+recovery catalog schema release 19.13.00.00. is newer than RMAN release
+RMAN>
+connected to target database: EBST (not mounted)
+RMAN> 2> 3> 4> 5> 6> 7> 8>
+allocated channel: ch1
+channel ch1: SID=406 device type=SBT_TAPE
+channel ch1: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
+sent command to channel: ch1
+Starting restore at Dec 28 2021 15:07:20
+released channel: ch1
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-03002: failure of restore command at 12/28/2021 15:07:20
+RMAN-06004: ORACLE error from recovery catalog database: RMAN-20005: target database name is ambiguous
+RMAN>
+Recovery Manager complete.
+]
+</code>
+Solution: Find the bad (probably latest?) DBID and unregister it.
+<code>
+select * from (
+    select rbs.db_key
+    ,      rd.name
+    ,      rbs.db_id
+    ,      rbs.bs_key
+    ,      rbs.recid
+    ,      rbs.stamp
+    ,      rbs.backup_type
+    ,      rbs.start_time
+    ,      status
+    from   rc_backup_set rbs
+    ,      rc_database   rd
+    where  rbs.db_key = rd.db_key
+    and    rbs.db_id  = rd.dbid
+    and    rd.name    = 'EBST'
+    order  by rbs.start_time desc
+    )
+where rownum <201
+/
+    DB_KEY NAME          DB_ID     BS_KEY      RECID      STAMP B START_TIME         S
+---------- -------- ---------- ---------- ---------- ---------- - ------------------ -
+ 741456984 EBST     3520647526  741457647          6 1092416606 D 021-12-27 17:03:23 A
+ 741456984 EBST     3520647526  741457646          5 1092416602 L 021-12-27 17:03:16 A
+ 741456984 EBST     3520647526  741457645          4 1092413930 D 021-12-27 16:18:48 A
+ 741456984 EBST     3520647526  741457644          3 1092413921 L 021-12-27 16:18:33 A
+ 741456984 EBST     3520647526  741457643          2 1092413741 D 021-12-27 16:15:40 A
+ 741456984 EBST     3520647526  741457642          1 1092413737 L 021-12-27 16:15:33 A
+ 551048814 EBST     3456740606  741424657      73972 1092398632 D 021-12-27 12:03:51 A
+ 551048814 EBST     3456740606  741424548      73971 1092398625 L 021-12-27 12:03:33 A
+ 551048814 EBST     3456740606  741419265      73970 1092395122 D 021-12-27 11:05:19 A
+ 551048814 EBST     3456740606  741419247      73969 1092395108 L 021-12-27 11:04:52 A
+ 551048814 EBST     3456740606  741414454      73968 1092391453 D 021-12-27 10:04:10 A
+ 551048814 EBST     3456740606  741414363      73967 1092391448 L 021-12-27 10:03:53 A
+ 551048814 EBST     3456740606  741409969      73966 1092387855 D 021-12-27 09:04:11 A
+ 551048814 EBST     3456740606  741409839      73965 1092387842 L 021-12-27 09:03:44 A
+ 551048814 EBST     3456740606  741405228      73964 1092384264 D 021-12-27 08:04:22 A
+ 551048814 EBST     3456740606  741405074      73963 1092384252 L 021-12-27 08:04:05 A
+ 551048814 EBST     3456740606  741402238      73962 1092382479 D 021-12-27 07:34:37 A
+ 551048814 EBST     3456740606  741402176      73961 1092382471 L 021-12-27 07:34:27 A
+ 551048814 EBST     3456740606  741399145      73960 1092380631 D 021-12-27 07:03:49 A
+ 551048814 EBST     3456740606  741398898      73959 1092380623 L 021-12-27 07:03:40 A
+ 551048814 EBST     3456740606  741394709      73958 1092377070 D 021-12-27 06:04:28 A
+</code>
+<code>
+RMAN> set dbid 3520647526;
+executing command: SET DBID
+database name is "EBST" and DBID is 3520647526
+RMAN> unregister database;
+database name is "EBST" and DBID is 3520647526
+Do you really want to unregister the database (enter YES or NO)? yes
+database unregistered from the recovery catalog
+RMAN> exit
+Recovery Manager complete.
+</code>
+==== RMAN-03009: failure of full resync command on default channel at <date/time> ====
+After a database duplicate from production to a lower environment, no rman commands were possible. Unable to even allocate a channel.
+<code>
+RMAN> resync catalog;
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-03002: failure of show command at 09/17/2021 09:28:44
+RMAN-03014: implicit resync of recovery catalog failed
+RMAN-03009: failure of full resync command on default channel at 09/17/2021 09:28:44
+ORA-01403: no data found
+RMAN>
+</code>
+The issue is unexpected but relatively simple to fix, even if it is a bit drastic. It seems the problem is that the database controlfile has become corrupted.
+<code>
+alter database backup controlfile to trace as '/tmp/control_&dbname.sql';
+</code>
+<code>
+declare
+    j number;
+    k number;
+    filepath varchar2(4000) := '/cln/acc/ora_data/crelaa1/redo';
+    sqlstr   varchar2(4000);
+begin
+    k := 1;
+    for logfile in (select member from v$logfile)
+    loop
+        j := mod(k,4)+1;
+        sqlstr := 'alter database rename file '''||logfile.member||''' to ''||filepath||j||'/redo'||k||'.log''';
+        --dbms_output.put_line(sqlstr);
+        execute immediate sqlstr;
+        k := k + 1;
+    end loop;
+    if k = 1 then
+        dbms_output.put_line('Online log already renamed');
+    end if;
+end;
+/
+</code>
+Now edit the file and remove the unwanted sections, shutdown the database, recreate the controlfile and all will work again.
+==== How to Recover from Loss Of Online Redo Log And ORA-312 And ORA-313 (Doc ID 117481.1) ====
+Complete loss of database following rm of all members of all redolog groups.
+ *  https://support.oracle.com/epmos/faces/DocumentDisplay?id=117481.1
+Oracle support requested following queries to be run
+<code>
+spool /tmp/recovery_3_info.txt
+set pagesize 20000
+set linesize 180
+set pause off
+set serveroutput on
+set feedback on
+set echo on
+set numformat 999999999999999
+alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
+archive log list;
+select name,dbid,controlfile_type,open_mode,log_mode,checkpoint_change#,archive_change#,database_role from v$database;
+select * from v$database_incarnation;
+col name for a75
+select * from v$restore_point;
+select flashback_on from v$database;
+select file#,name,status,enabled from v$datafile;
+select file#,name,status,enabled from v$tempfile;
+select TS#,NAME,INCLUDED_IN_DATABASE_BACKUP,FLASHBACK_ON from v$tablespace;
+select * from v$recover_file;
+select * from v$backup;
+select member from v$logfile;
+select * from v$log;
+select * from v$logfile;
+select file#,name,recover,fuzzy,checkpoint_change#,creation_change#,checkpoint_time,creation_time,RESETLOGS_TIME,status from v$datafile_header;
+select status, to_char(checkpoint_change#), to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
+count(*) from v$datafile_header
+group by status, checkpoint_change#, checkpoint_time order by status, checkpoint_change#, checkpoint_time;
+select count(*),fhsta from x$kcvfh group by fhsta;
+select count(*),fhrba_seq from x$kcvfh group by fhrba_seq;
+select count(*),fhscn from x$kcvfh group by fhscn;
+select count(*),fhafs from x$kcvfh group by fhafs;
+select min(FHSCN) "LOW FILEHDR SCN" , max(FHSCN) "MAX FILEHDR SCN", max(FHAFS) "Min PITR ABSSCN" from X$KCVFH ;
+select fhdbn,fhdbi,hxfil,fhsta,fhscn,fhafs,fhrba_seq,fhtnm tbs_name from x$kcvfh;
+spool off
+</code>
+==== ORA-00392: log 10 of thread 1 is being cleared, operation not allowed ====
+Cannot open the database with resetlogs after a clone process using RMAN duplicate.\\
+In the alertlog we get pointed to a tracefile which hints at the problem
+<code>
+ORA-00313: open failed for members of log group 1 of thread 1
+ORA-00312: online log 1 thread 1: '/cln/exp/ora_data1/dwh/redolog/redo01.log'
+ORA-27037: unable to obtain file status
+IBM AIX RISC System/6000 Error: 2: No such file or directory
+Additional information: 3
+</code>
+The issue is that the clone process failed to transform the source path names into destination path names and so obviously the redolog files don't exist where the controlfile thinks they do.\\
+''''Solution''''
+The answer is to simply rename the files while the database is mounted
+<code>
+SQL> select open_mode from v$database;
+OPEN_MODE
+--------------------
+MOUNTED
+SQL> alter database rename file '/cln/exp/ora_data1/dwh/redolog/redo01.log' to '/cln/tst/ora_data1/dwht/redolog/redo01.log';
+Database altered.
+SQL> alter database rename file '/cln/exp/ora_data1/dwh/redolog/redo02.log' to '/cln/tst/ora_data1/dwht/redolog/redo02.log';
+Database altered.
+SQL> alter database rename file '/cln/exp/ora_data1/dwh/redolog/redo03.log' to '/cln/tst/ora_data1/dwht/redolog/redo03.log';
+Database altered.
+</code>
+As we are going to open the database with resetlogs, it doesn't matter where they are at the moment, so we can do this a bit quicker
+<code>
+Attempting to open the database shows another issue
+<code>
+SQL> alter database open resetlogs;
+alter database open resetlogs
+*
+ERROR at line 1:
+ORA-00392: log 3 of thread 1 is being cleared, operation not allowed
+ORA-00312: online log 3 thread 1: '/cln/exp/ora_data1/dwh/redolog/redo03.log'
+</code>
+Look at the status of the redologs
+<code>
+SQL> select group#,thread#,status from v$log;
+    GROUP#    THREAD# STATUS
+---------- ---------- ----------------
+          1 CLEARING
+          1 CLEARING
+          1 CLEARING_CURRENT
+rows selected.
+</code>
+Manually clear out the offending logfile
+<code>
+SQL> alter database clear logfile group 3;
+Database altered.
+SQL> select group#,thread#,status from v$log;
+    GROUP#    THREAD# STATUS
+---------- ---------- ----------------
+          1 CLEARING
+          1 CLEARING
+          1 CURRENT
+rows selected.
+</code>
+Try to open the database again
+<code>
+SQL> alter database open resetlogs;
+Database altered.
+</code>
+Disco!
+==== ORA-12003: materialized view or zonemap "KPK"."EVD01_NEW" does not exist ====
+Here's one you shouldn't see very often.\\
 Trying to drop a tablespace but prevented from doing so because a materialized view has referential contraints on a table in this tablespace.
-<code>38@@</code>
+<code>
+SQL> drop tablespace TS_THALER_PART_OLD including contents and datafiles cascade constraints;
+drop tablespace TS_THALER_PART_OLD including contents and datafiles cascade constraints
+  *
+ERROR at line 1:
+ORA-00604: error occurred at recursive SQL level 1
+ORA-12083: must use DROP MATERIALIZED VIEW to drop "KPK"."EVD01_NEW"
+</code>
 Ok, nice of Oracle to tell us what we have to do. Simple enough. Let's drop the materialized view.
-<code>39@@</code>
+<code>
+SQL> DROP MATERIALIZED VIEW "KPK"."EVD01_NEW";
+DROP MATERIALIZED VIEW "KPK"."EVD01_NEW"
+  *
+ERROR at line 1:
+ORA-12003: materialized view or zonemap "KPK"."EVD01_NEW" does not exist
+</code>
 Aah. Not so obvious then.
-<code>40@@</code>
+<code>
-It exists... but as a table.<br />
+desc "KPK"."EVD01_NEW"
-Something has gone wrong in the database somewhere. As it turns out after some deeper investigation, this database is a copy of production but "with modifications".<br />
+ Name                                      Null?    Type
-After the cloning process, a schema needed renaming. Oracle have a procedure for this but it is very long-winded and time-consuming. It uses export and import with transportable tablespaces to do the remap of the schema.<br />
+ ----------------------------------------- -------- ----------------------------
-An unsupported workaround to rename schema is to simply update the username in sys.user$ and restart the database.<br />
+ DATMAJ                                             VARCHAR2(8 CHAR)
+ HEUMAJ                                             VARCHAR2(8 CHAR)
+ PGMMAJ                                             VARCHAR2(6 CHAR)
+ NATOPN                                             VARCHAR2(6 CHAR)
+ REFEXN                                             VARCHAR2(50 CHAR)
+ SWIOLN                                             VARCHAR2(1 CHAR)
+ STAEVT                                             VARCHAR2(4 CHAR)
+ REFEVT                                             VARCHAR2(16 CHAR)
+ NUMIDT                                             VARCHAR2(30 CHAR)
+ ORIEVT                                             VARCHAR2(10 CHAR)
+ REFLOG                                             VARCHAR2(16 CHAR)
+ MSGEVT_CPL                                         VARCHAR2(1500 CHAR)
+ REFOPN                                             VARCHAR2(16 CHAR)
+ DATEVT                                             VARCHAR2(8 CHAR)
+ MSGEVT_FIL_1                                       VARCHAR2(2000 CHAR)
+ MSGEVT_FIL_2                                       VARCHAR2(2000 CHAR)
+ MSGEVT_SPE                                         VARCHAR2(200 CHAR)
+ DATEFF                                             VARCHAR2(8 CHAR)
+ TMSTMP                                             VARCHAR2(15 CHAR)
+ VEREVT                                             VARCHAR2(3 CHAR)
+ MSGEVT_FIL_3                                       VARCHAR2(2000 CHAR)
+ MSGEVT_FIL_4                                       VARCHAR2(2000 CHAR)
+ TYPEVT                                             VARCHAR2(5 CHAR)
+ SBP_CODAPP                                         VARCHAR2(10 CHAR)
+ SBP_REFINT                                         VARCHAR2(36 CHAR)
+ SBP_TYPACT                                         VARCHAR2(2 CHAR)
+</code>
+It exists... but as a table.\\
+Something has gone wrong in the database somewhere. As it turns out after some deeper investigation, this database is a copy of production but "with modifications".\\
+After the cloning process, a schema needed renaming. Oracle have a procedure for this but it is very long-winded and time-consuming. It uses export and import with transportable tablespaces to do the remap of the schema.\\
+An unsupported workaround to rename schema is to simply update the username in sys.user$ and restart the database.\\
 This works for the most part but now we see a consequence of that action.
-<code>41@@</code>
+<code>
-Ahaa, there's the materialized view but the owner is different from the one described by the database error message above. This one is the owner as it was on production.<br />
+col owner      for a30
-This means the owner of materialized views has been denormalised for some reason. Reading the (complex) view text of dba_mviews, we see the owner column is based on sys.snap$.sowner<br />
+col mview_name for a30
-<code>42@@</code>
+select owner
+,      mview_name
+from   dba_mviews
+/
+OWNER                          MVIEW_NAME
+------------------------------ ------------------------------
+EPK                            EVD01_NEW
+</code>
+Ahaa, there's the materialized view but the owner is different from the one described by the database error message above. This one is the owner as it was on production.\\
+This means the owner of materialized views has been denormalised for some reason. Reading the (complex) view text of dba_mviews, we see the owner column is based on sys.snap$.sowner\\
+<code>
+col sowner for a20
+col vname  for a20
+col tname  for a20
+select sowner
+,      vname
+,      tname
+from   sys.snap$
+/
+SOWNER               VNAME                TNAME
+-------------------- -------------------- --------------------
+EPK                  EVD01_NEW            EVD01_NEW
+</code>
 For future reference when updating the username in sys.user$, update the snapshot owner in this table as well!
-<code>43@@</code>
+<code>
+update sys.snap$
+set    sowner = 'KPK'
+where  sowner = 'EPK'
+/
+</code>
 Try again
-<code>44@@</code>
+<code>
+DROP MATERIALIZED VIEW "KPK"."EVD01_NEW";
+Materialized view dropped.
+</code>
 yep, and the tablespace?
-<code>45@@</code>
+<code>
+drop tablespace TS_THALER_PART_OLD including contents and datafiles cascade constraints;
+Tablespace dropped.
+</code>
-=====ORA-12162: TNS:net service name is incorrectly specified=====
+==== ORA-12162: TNS:net service name is incorrectly specified ====
 Probably nothing to do with TNS. Check your ORACLE_SID is set correctly!
-=====ORA-00054: resource busy and acquire with NOWAIT specified=====
+==== ORA-00054: resource busy and acquire with NOWAIT specified ====
-<code>46@@</code>
+<code>
-<code>47@@</code>
+SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
+S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT
+FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S,
+V$PROCESS P, V$SQL SQ
+WHERE L.OBJECT_ID = O.OBJECT_ID
+AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR
+AND S.SQL_ADDRESS = SQ.ADDRESS;
+</code>
+<code>
+select object_name, s.sid, s.serial#, p.spid
+from v$locked_object l, dba_objects o, v$session s, v$process p
+where l.object_id = o.object_id and l.session_id = s.sid and s.paddr = p.addr;
+</code>
 then
-<code>48@@</code>
+<code>
+alter system kill session 'sid,serial#';
+</code>
+==== ORA-12516: TNS:listener could not find available handler with matching ====
+Database was running low on (and eventually out of) processes.\\
+Waited until the db would allow sysdba logons and increased the processes parameter.\\
+ * Increased processes from 300 to 600\\
+sessions is automatically adjusted to (1.5 * processes) + 22 if not set\\
+transactions is automatically adjusted to (1.1 * processes) if not set\\
+Also statically registered the database with the listener in listener.ora
+==== Unable to uninstall old version of oracle.ahf ====
+<code>
+echo "Y"|"${ORACLE_BASE}/oracle.ahf/bin/tfactl" uninstall
+AHF Uninstallation Log : /tmp/ahf_uninstall_24317334_2024_06_28-15_09_44.log
+AHF Home Directory not found. Exiting uninstall ..
+</code>
+The problem was that this is a PowerHA cluster and when AHF was installed, it was on the other node. Now, after a failover, the original data subdirectories no longer match the current hostname.
+The solution is to either failback :-) or to rebuild what is missing by:
+<code>
+${ORACLE_BASE}/oracle.ahf/tfa/bin/tfactl -standalone
+</code>
-=====ORA-12518: TNS:listener could not hand off client connection=====
+==== ORA-12518: TNS:listener could not hand off client connection ====
 The database you are trying to connect to is probably on its knees. Check server memory is not exhausted.
-=====ORA-12520: TNS:listener could not find available handler for requested type of server=====
+==== ORA-12519: TNS:no appropriate service handler found" in listener log file ====
+and its associated TNS-12519: TNS:no appropriate service handler found" in listener log file in the listener log file.\\
+Oh, this one took me days to find!\\
+% of the solutions point to increasing the processes on the instance but this was definitely not the case here. Average number of processes 120 with a max. of 900.\\
+Even with the related story of pmon not updating the listener with terminated process count is not a good enough excuse to increase processes here.\\
+The clue was that connections using service_name were succeeding but connections using sid were not.\\
+The situation here is that we have a cluster of 2 nodes. The parent is called hn481 and the children are called hn5114 and hn5214.\\
+All the entries in tnsnames.ora are setup with the parent node, hn481.\\
+Checked the listener.ora file and looking at the listener definition it is showing a host connection to hn5114. This is actually the node where all the databsaes are running - but it does not resolve to the same address as the parent (virtual) node!\\
+Change this entry to match the tns entries, hn481, restart the listener and all is working again!\\
+==== ORA-12520: TNS:listener could not find available handler for requested type of server ====
 Strangely this could be related to number of processes being exhausted.
-<code>49@@</code>
+<code>
+show parameter processes
+alter system set processes=350 scope=spfile;
+shutdown immediate
+startup
+</code>
-=====ORA-12541: TNS: no listener=====
+==== ORA-12541: TNS: no listener ====
-Either very basic - start the listener!<br />
+Either very basic - start the listener!\\
-or, as in our case - not so obvious.<br />
+or, as in our case - not so obvious.\\
-We use virtual IP addresses so that the IP address stays the same after failover to standby.<br />
+We use virtual IP addresses so that the IP address stays the same after failover to standby.\\
-Due to some confused configuration, we got the network adapter on two different servers to have the same IP address.<br />
+Due to some confused configuration, we got the network adapter on two different servers to have the same IP address.\\
 Stop the IP address on the bad server (assign another one) and all is fine again.
-=====ORA-12514: TNS:listener does not currently know of service=====
+==== ORA-12514: TNS:listener does not currently know of service ====
-<code>50@@</code>
+<code>
+lsnrctl status <listener_name>
+</code>
 Been altering tnsnames.ora or listener.ora?
-  * Check the local_listener parameter
+  *  Check the local_listener parameter
-Try resetting it to itself. Strange but if you are not using port 1521, this can work wonders!<br />
+Try resetting it to itself. Strange but if you are not using port 1521, this can work wonders!\\
 Assuming listener is called LISTENER_WM9T:
-<code>51@@</code>
+<code>
-  * Try and connect with the service name or directly with
+alter system set local_listener=LISTENER_WM9T scope=both;
-<code>52@@</code>
+</code>
-Excellent description of how Oracle TNS listeners work and a checklist on how to fix this error<br />
+  *  Try and connect with the service name or directly with
-  * [[http://edstevensdba.wordpress.com/2011/03/19/ora-12514/ TNS — Ed Stevens]]
+<code>
+sqlplus 'sys/*******'@<host>><port>/<SID> as sysdba
+</code>
+Excellent description of how Oracle TNS listeners work and a checklist on how to fix this error\\
+  *  [[http://edstevensdba.wordpress.com/2011/03/19/ora-12514/|TNS — Ed Stevens]]
+==== ORA-12547: TNS lost contact ====
-=====ORA-12547: TNS lost contact=====
 If
-<code>53@@</code>
+<code>
+sqlplus <user>/<pass>
+</code>
 fails with above error, try
-<code>54@@</code>
+<code>
-If this works, it is most probably a permissions error.<br />
+sqlplus <user>/<pass>@<db connection>
+</code>
+If this works, it is most probably a permissions error.\\
 Re-run the
-<code>55@@</code>
+<code>
+$ORACLE_HOME/root.sh
+</code>
 script that was run as part of the original installation. This will reset the permissions on some important files.
-=====ORA-02374: conversion error loading table "BLENGADM"."ZZ_EVTCTA"=====
+==== ORA-02374: conversion error loading table "BLENGADM"."ZZ_EVTCTA" ====
 This is typical of importing data with Datapump when the tables in the source database were created with "byte" semantics and the destination is "char" semantics.
-<code>56@@</code>
+<code>
-====Problem====
+ORA-02374: conversion error loading table "BLENGADM"."ZZ_EVTCTA"
+ORA-12899: value too large for column NOMCTA (actual: 257, maximum: 256)
+ORA-02372: data for row: NOMCTA : 0X'4C6976726574206427E9706172676E65202020202020202020'
+</code>
+=== Problem ===
 The root cause is the nls_length_semantics of the table columns being BYTE where one character is assigned one byte. But after conversion in a multi-byte database, one character is larger than one byte (could be 2, 3 or 4) and no longer fits.
-====Solution====
+=== Solution ===
-  * import the metadata
+  *  import the metadata
 Run the impdp command with content=metadata_only. This will create all the tables (still in their original form) but not the data.
-  * alter the BYTE columns in the affected tables to CHAR semantics
+  *  alter the BYTE columns in the affected tables to CHAR semantics
-<code>57@@</code>
+<code>
-  * Import the data
+select column_name
+,      char_used
+,      data_length
+,      data_type
+from   user_tab_columns
+where  table_name = 'T'
+and    char_used = 'B';
+COLUMN_NAME  C DATA_LENGTH DATA_TYPE
+------------ - ----------- ---------
+X            B           1 VARCHAR2
+SQL> alter table t modify x varchar2(1 char);
+Table altered.
+</code>
+  *  Import the data
 Rerun the impdp ensuring table_exists_action is not REPLACE (use truncate or append). This now imports the data into the modified tables.
-====This script does the donkey work of altering table column semantics from byte to char====
+=== This script does the donkey work of altering table column semantics from byte to char ===
 The author of this script is unknown but he/she deserves a medal. It saves countless hours of work and is so well written.
-<code>58@@</code>
+<code>
+/*
+  The script converts all the VARCHAR2 columns in the user's schema
+  to CHAR length semantics. Known to work on 10.2.0.4 and later.
-=====ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION=====
+  Script must be executed against the database without any other client
-Also can be <tt>ORA-54033: column to be modified is used in a virtual column expression</tt><br />
+  and/or application connections to the affected schema.
-Nothing especially to do with partitions - just any ddl operation that affects the table.<br />
+  Oracle does not support CHAR semantics for the built-in schemas,
+  like SYS, SYSTEM or SYSMAN, so the script will throw an error,
+  if attempt to run it against any one of them.
+  Upon completion database objects that have dependencies on tables
+  being modified will be invalidated. Oracle usually re-compiles invalidated
+  objects on the first access. Alternatively, a DBA can execute UTLPRP script
+  to recompile all invalid objects in the entire database.
+  Upon completion, please make sure that NLS_LENGTH_SEMANTICS instance parameter
+  is set to CHAR to ensure further upgradability of the schema.
+  * /
+set serveroutput on size unlimited format wrap
+declare
+  type sql_list_t is table of varchar2(32767) index by pls_integer;
+  type idx_list_t is table of user_indexes.index_name%type index by pls_integer;
+  type col_list_t is table of user_tab_columns%rowtype index by pls_integer;
+  --
+  lv_col_cnt   pls_integer := 0;
+  lv_idx_cnt   pls_integer := 0;
+  lv_char_set  pls_integer := 0;
+  lv_user      user_users.username%type;
+  lv_sql_stmt  varchar2(32767);
+  lv_byte_cols col_list_t;
+  lv_idx_names idx_list_t;
+  lv_idx_ddls  sql_list_t;
+  --
+  procedure run_sql_stmt
+  (
+   p_sql_stmt in varchar2
+  )
+  as
+  begin
+    execute immediate p_sql_stmt;
+  exception
+    when others
+    then
+      raise_application_error(-20202, 'Failed statement [[' || p_sql_stmt || dbms_utility.format_error_backtrace() || ']]', true);
+  end;
+begin
+  --
+  lv_user := user();
+  if lv_user in ('SYS', 'SYSTEM', 'SYSMAN')
+  then
+    raise_application_error(-20202, 'This script cannot be executed against Oracle built-in schema [[' || lv_user || ']].');
+  end if;
+  --
+  select
+         *
+         bulk collect into lv_byte_cols
+  from
+         user_tab_columns uc
+  where
+         uc.char_used = 'B'
+         and
+         substr(uc.table_name, 1, 4) != 'BIN$'
+         and
+         not exists
+         (
+          select 1
+          from   user_views uv
+          where  uv.view_name = uc.table_name
+         )
+  ;
+  --
+  lv_col_cnt := lv_byte_cols.count();
+  --
+  dbms_output.enable();
+  --
+  if lv_col_cnt > 0
+  then
+    for idx_rw in
+    (
+     select index_name
+     from   user_indexes
+     where  index_type = 'FUNCTION-BASED NORMAL'
+    )
+    loop
+      lv_idx_cnt := lv_idx_cnt + 1;
+      lv_idx_names(lv_idx_cnt) := idx_rw.index_name;
+      lv_idx_ddls(lv_idx_cnt) := replace
+                                 (
+                                  dbms_metadata.get_ddl
+                                  (
+                                   object_type => 'INDEX',
+                                   name        => idx_rw.index_name
+                                  )
+                                  ,
+                                  ';'
+                                 );
+      lv_sql_stmt := 'drop index ' || idx_rw.index_name;
+      run_sql_stmt(lv_sql_stmt);
+      dbms_output.put_line('Dropped function-based index: ' || idx_rw.index_name);
+      dbms_output.put_line('In case of script failure, restore the index by running the DDL statement below:');
+      dbms_output.put_line(lv_idx_ddls(lv_idx_cnt) || ';');
+    end loop;
+    --
+    for i in 1..lv_col_cnt
+    loop
+      lv_sql_stmt :=  'alter table ' || lv_byte_cols(i).table_name || ' modify (' ||
+                      lv_byte_cols(i).column_name || ' ' || lv_byte_cols(i).data_type ||
+                      '(' || lv_byte_cols(i).char_length || ' char))';
+      run_sql_stmt(lv_sql_stmt);
+    end loop;
+    --
+    dbms_output.new_line();
+    for i in 1..lv_idx_cnt
+    loop
+      run_sql_stmt(lv_idx_ddls(i));
+      dbms_output.put_line('Recreated function-based index: ' || lv_idx_names(i));
+    end loop;
+    --
+    dbms_output.new_line();
+    dbms_output.put_line
+    (
+     'Updated length semantics to CHAR for ' || lv_col_cnt || ' column' ||
+     case when lv_col_cnt > 1
+          then 's'
+     end ||
+     ' in the schema [[' || lv_user || ']].'
+    );
+  else
+    dbms_output.put_line('No columns with BYTE length semantics were found in the schema [[' || lv_user || ']].');
+  end if;
+  --
+  select count(*) into lv_char_set
+  from   nls_session_parameters
+  where  parameter = 'NLS_LENGTH_SEMANTICS'
+         and
+         value = 'CHAR'
+  ;
+  --
+  if lv_char_set = 0
+  then
+    dbms_output.put_line('WARNING: Do not forget to change instance parameter NLS_LENGTH_SEMANTICS to CHAR and restart the database.');
+  end if;
+end;
+/
+</code>
+==== ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION ====
+Also can be <tt>ORA-54033: column to be modified is used in a virtual column expression</tt>\\
+Nothing especially to do with partitions - just any ddl operation that affects the table.\\
 Hidden columns do not normally show up in a desc unless colinvisible is on
-<code>59@@</code>
+<code>
+set colinvisible on
+</code>
 But these sneaky system-generated ones don't show up even with that setting on!  You need to show them with this:
-<code>60@@</code>
+<code>
-That last row means extended statistics have been generated for 2 columns in this table.<br />
+set pages 50 lines 1000
-You can see this because the name begins with SYS_STS. If it begins with SYS_<something else> then it is not extended statistics.<br />
+col column_name for a40
+col data_default for a40
+select column_id
+,      column_name
+,      data_default
+,      user_generated
+,      hidden_column
+,      virtual_column
+,      identity_column
+from   dba_tab_cols
+where  owner      = upper('&owner.')
+and    table_name = upper('&table_name.')
+/
+INTERNAL_COLUMN_ID COLUMN_NAME                                        DATA_DEFAULT                                       HIDDEN_COLUM VIRTUAL_COLU
+------------------ -------------------------------------------------- -------------------------------------------------- ------------ ------------
+MONLOT_REC_CDR                                     '0'                                                NO           NO
+MONLOT_REC_DBT                                     '0'                                                NO           NO
+MONLOT_CAL_CDR                                     '0'                                                NO           NO
+MONLOT_CAL_DBT                                     '0'                                                NO           NO
+MONMVT_CPT                                         '0'                                                NO           NO
+SENNOT                                             ' '                                                NO           NO
+NUMSEQ                                                                                                NO           NO
+NOMFIC                                                                                                NO           NO
+TOTCPT_REC                                                                                            NO           NO
+TOTCPT_CAL                                                                                            NO           NO
+MONLOT_REC                                                                                            NO           NO
+MONLOT_CAL                                                                                            NO           NO
+NBRELE_REC                                                                                            NO           NO
+NBRELE_CAL                                                                                            NO           NO
+NUMCPT_GLO                                                                                            NO           NO
+DEVLOT                                                                                                NO           NO
+CODERR                                                                                                NO           NO
+CANLOT                                                                                                NO           NO
+REFTEX                                                                                                NO           NO
+REFEXN                                                                                                NO           NO
+ETTLOT                                                                                                NO           NO
+TYPLOT                                                                                                NO           NO
+DATEXC                                                                                                NO           NO
+STALOT                                                                                                NO           NO
+REFLOT                                                                                                NO           NO
+USRAUT                                                                                                NO           NO
+HEUAUT                                                                                                NO           NO
+DATAUT                                                                                                NO           NO
+PGMMAJ                                                                                                NO           NO
+USRMAJ                                                                                                NO           NO
+HEUMAJ                                                                                                NO           NO
+DATMAJ                                                                                                NO           NO
+DATCRT                                                                                                NO           NO
+REFMAJ                                                                                                NO           NO
+NUMVER                                                                                                NO           NO
+SYS_STS#KR2WNV5L070XKV1L5H7REH                     SYS_OP_COMBINED_HASH("NOMFIC","NUMSEQ")            YES          YES
+rows selected.
+</code>
+That last row means extended statistics have been generated for 2 columns in this table.\\
+You can see this because the name begins with SYS_STS. If it begins with SYS_<something else> then it is not extended statistics.\\
 It may be an unused column. Original [[https://dba.stackexchange.com/questions/196248/what-is-the-sys-c00054-140314103403-column-in-my-regular-oracle-table|here]]
-<code>61@@</code>
+<code>
+create table t1 as select * from dba_roles;
-====Solution====
+Table created.
+desc t1
+ Name                                      Null?    Type
+ ----------------------------------------- -------- ----------------------------
+ ROLE                                      NOT NULL VARCHAR2(30)
+ PASSWORD_REQUIRED                                  VARCHAR2(8)
+ AUTHENTICATION_TYPE                                VARCHAR2(11)
+alter table t1 set unused (AUTHENTICATION_TYPE);
+Table altered.
+desc t1
+ Name                                      Null?    Type
+ ----------------------------------------- -------- ----------------------------
+ ROLE                                      NOT NULL VARCHAR2(30)
+ PASSWORD_REQUIRED                                  VARCHAR2(8)
+select column_name from dba_tab_cols where owner = '&owner.' and table_name = '&table_name.';
+COLUMN_NAME
+------------------------------
+SYS_C00003_18012517:25:39$
+PASSWORD_REQUIRED
+ROLE
+SQL> alter table t1 drop unused columns;
+Table altered.
+select column_name from dba_tab_cols where owner = '&owner.' and table_name = '&table_name.';
+COLUMN_NAME
+------------------------------
+PASSWORD_REQUIRED
+ROLE
+</code>
+=== Solution ===
 Drop the extended stats, do what you need to do and recreate the extended stats.
-<code>62@@</code>
+<code>
+PROCEDURE DROP_EXTENDED_STATS
+ Argument Name                  Type                    In/Out Default?
+ ------------------------------ ----------------------- ------ --------
+ OWNNAME                        VARCHAR2                IN
+ TABNAME                        VARCHAR2                IN
+ EXTENSION                      VARCHAR2                IN
+exec dbms_stats.drop_extended_stats ( 'KPK', 'XND40', '("NOMFIC","NUMSEQ")' );
+create table kpk.xnd40_new as select * from kpk.xnd40 where 1=2;
+Table created.
+select dbms_stats.create_extended_stats ( 'KPK', 'XND40', '("NOMFIC","NUMSEQ")' ) from   dual;
+</code>
 The stats columns can also be seen in their own table
-<code>63@@</code>
+<code>
+set lines 1000 pages 100
+col owner for a6
+col table_name for a20
+col extension_name  for a40
+col extension  for a50
+select owner, table_name, extension_name, extension, creator, droppable from dba_stat_extensions where owner = upper('&&owner.') and table_name = upper('&&table_name.') and extension_name LIKE 'SYS_STS%';
-=====ORA-14404: partitioned table contains partitions in a different tablespace=====
+OWNER TABLE_NAME           EXTENSION_NAME                           EXTENSION                                          CREATOR                  DROPPABLE
+----- -------------------- ---------------------------------------- -------------------------------------------------- ------------------------ ------------
+KPK   XMD01                SYS_STSOA4UXZDBW7MIV5OOR8VOHHK           ("DATCRT","CODJRN")                                SYSTEM                   YES
+KPK   XDD02                SYS_STSFJCZYRZBCQL820ADIR0Y4RZ           ("REFMSG","CPTTIT","TYPMSG")                       SYSTEM                   YES
+KPK   XCD09                SYS_STS2OIO647GN0_UV$XO583KKKJ           ("DATCRT","HEUMAJ","STAMOD")                       SYSTEM                   YES
+KPK   XCD09                SYS_STS_BRDODC6DJG_HXK0#O9BOYF           ("STAMOD","CODMOD")                                SYSTEM                   YES
+KPK   XCD09                SYS_STS2#DY5U$WQG6COVHB##H2ESK           ("DATMAJ","STAMOD","CODMOD")                       SYSTEM                   YES
+KPK   XCD09                SYS_STSO7#D3DI5R8_VAF$WJ32D8MT           ("STAMOD","KEYREF_1")                              SYSTEM                   YES
+KPK   XCD09                SYS_STS4FRY10M34S2L$0FDM1N4B18           ("STAMOD","IDTMOD")                                SYSTEM                   YES
+</code>
+==== ORA-00942: table or view does not exist ====
+When performing DST upgrade, following errors occur. It's very confusing and misleading because the simple fact is that the temporary tablespace has probably not been created (properly).
+Most often seen after a database duplication and so probably not checked the temp tablespace yet!
+<code>
+starting check
+Session altered.
+INFO: Starting with RDBMS DST update preparation.
+INFO: NO actual RDBMS DST update will be done by this script.
+INFO: If an ERROR occurs the script will EXIT sqlplus.
+INFO: Doing checks for known issues ...
+INFO: Database version is 19.0.0.0 .
+INFO: Database RDBMS DST version is DSTv41 .
+INFO: No known issues detected.
+INFO: Now detecting new RDBMS DST version.
+A prepare window has been successfully started.
+INFO: Newest RDBMS DST version detected is DSTv42 .
+INFO: Next step is checking all TSTZ data.
+INFO: It might take a while before any further output is seen ...
+ERROR: Something went wrong during DBMS_DST.FIND_AFFECTED_TABLES.
+A prepare window has been successfully ended.
+Error code -604: ORA-00604: error occurred at recursive SQL level 1
+DECLARE
+*
+ERROR at line 1:
+ORA-20091: Stopping script - see previous message ...
+ORA-06512: at line 36
+DECLARE
+*
+ERROR at line 1:
+ORA-00942: table or view does not exist
+ORA-06512: at line 4
+Session altered.
+</code>
+==== ORA-14404: partitioned table contains partitions in a different tablespace ====
 As part of an RMAN duplicate with a skip tablespace, this error is produced
-<code>64@@</code>
+<code>
+Reenabling controlfile options for auxiliary database
+Executing: alter database enable block change tracking using file '/oracle/acc/ora_data3/adsa1/bct_adsp_do_not_delete.f'
+contents of Memory Script:
+{
+   Alter clone database open resetlogs;
+}
+executing Memory Script
+database opened
+Dropping offline and skipped tablespaces
+Executing: drop tablespace "ADS_ARCHIVE" including contents cascade constraints
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-03002: failure of Duplicate Db command at 10/16/2017 23:46:12
+RMAN-05501: aborting duplication of target database
+RMAN-06136: ORACLE error from auxiliary database: ORA-14404: partitioned table contains partitions in a different tablespace
+Recovery Manager complete.
+</code>
 and when attempting to backup the database, we get errors
-<code>65@@</code>
+<code>
-====Problem====
+RMAN-06169: could not read file header for datafile 188 error reason 1
-Tables in the tablespace have partitions in another tablespace so this one cannot be dropped.<br />
+RMAN-06169: could not read file header for datafile 189 error reason 1
+RMAN-06169: could not read file header for datafile 190 error reason 1
+RMAN-06169: could not read file header for datafile 191 error reason 1
+RMAN-06169: could not read file header for datafile 14 error reason 1
+released channel: t1
+released channel: t2
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-03002: failure of backup plus archivelog command at 10/17/2017 15:08:40
+RMAN-06056: could not access datafile 14
+Recovery Manager complete.
+</code>
+=== Problem ===
+Tables in the tablespace have partitions in another tablespace so this one cannot be dropped.\\
 Check which table partitions are in which tablespaces
-<code>66@@</code>
+<code>
-<code>67@@</code>
+set lines 200
+set pagesize 200
+col table_name for a14
+col table_owner for a14
+col partition_name for a14
+select table_owner
+,      table_name
+,      partition_name
+,      tablespace_name
+from   dba_tab_partitions
+where  (table_owner, table_name) in
+       (
+       select table_owner
+       ,      table_name
+       from   dba_tab_partitions x
+       where  x.tablespace_name = '&&TABLESPACE_TO_DROP'
+       and    exists ( select *
+                       from   dba_tab_partitions y
+                       where  x.table_owner = y.table_owner
+                       and    x.table_name  = y.table_name
+                       and    y.tablespace_name not in ('&TABLESPACE_TO_DROP')
+                       group  by table_owner
+                       ,      table_name
+                     )
+        )
+order by 1,2,partition_position
+/
+undef TABLESPACE_TO_DROP
+</code>
+<code>
+TABLE_OWNER    TABLE_NAME     PARTITION_NAME TABLESPACE_NAME
+-------------- -------------- -------------- ------------------------------
+KPK            XND20          P0910          TS_THALER_PART_OLD
+KPK            XND20          P1112          TS_THALER_PART_OLD
+KPK            XND20          P13            TS_THALER_PART_OLD
+KPK            XND20          P14            TS_THALER_PART_OLD
+KPK            XND20          P15            TS_THALER_PART_OLD
+KPK            XND20          P16            TS_THALER_DATA
+KPK            XND20          P17            TS_THALER_DATA
+KPK            XND20          P18            TS_THALER_DATA
+KPK            XND20          PMAX           TS_THALER_DATA
+</code>
-====Solution====
+=== Solution ===
 If the intention is to keep all the tables then run this query to generate statements to move the relevant tables out of this tablespace
-<code>68@@</code>
+<code>
-If however, the intention is to drop the tablespace anyway, the tables need to be dropped before the tablespace can be dropped.<br />
+select 'alter table '||b.table_owner||'.'||b.table_name||' move partition '||b.partition_name||' tablespace &NEW_TABLESPACE;'
-But ...tables with partitions in different tablespaces cannot be dropped. The partitions need to be dropped first!<br />
+from   (
+       select distinct table_name
+       ,      partition_name
+       from   dba_tab_partitions
+       where  tablespace_name = '&&TABLESPACE_NAME'
+       )                  a
+,      dba_tab_partitions b
+where  a.table_name       = b.table_name
+and    b.tablespace_name != '&TABLESPACE_NAME'
+/
+undef TABLESPACE_NAME
+</code>
+If however, the intention is to drop the tablespace anyway, the tables need to be dropped before the tablespace can be dropped.\\
+But ...tables with partitions in different tablespaces cannot be dropped. The partitions need to be dropped first!\\
 Run the following query to generate statements to drop all the 'problem' tables. When these have gone, the tablespace can be dropped.
-  * drop table partitions
+  *  specific script to generate drop commands for all partitions and tables for a customers tablespace...
-<code>69@@</code>
+<code>
-  * drop tables
+SELECT exec_statement
-<code>70@@</code>
+FROM   (SELECT      'exec ADS.P_ROTATE_LIB.P_DROP_PARTITION('''
-  * drop tablespace
+                 || table_owner
-<code>71@@</code>
+                 || ''''
-====Find all table partitions in tablespaces with missing datafiles====
+                 || ','''
-<code>72@@</code>
+                 || table_name
-====Use exchange partition to alter metadata of the tablespaces without moving the actual data====
+                 || ''','''
-  * [[https://community.oracle.com/message/2166242#2166242|this]] is an explanation of what you are looking for!<br />
+                 || partition_name
+                 || ''',1);'
+                     AS exec_statement
+        FROM     dba_tab_partitions
+        WHERE    table_owner || table_name IN
+                     (
+                         SELECT table_owner || table_name
+                         FROM   dba_tab_partitions
+                         WHERE  table_owner = 'ADS' AND tablespace_name <> 'ADS'
+                     )
+        ORDER BY table_name, partition_name DESC)
+UNION ALL
+SELECT DISTINCT 'drop table ' || table_owner || '.' || table_name || ';' AS exec_statement
+FROM   dba_tab_partitions
+WHERE  table_owner = 'ADS' AND tablespace_name <> 'ADS'
+/
+</code>
+or individually...
+  *  drop table partitions
+<code>
+spool /tmp/drop_table_partitions.sql
+sqlplus / as sysdba<<EOSQL
+set headi off newpa none feedb off trims on echo off
+select 'alter table '||table_owner||'.'||table_name||' drop partition '||partition_name||';'
+from   dba_tab_partitions
+where  tablespace_name = '$TABLESPACE_NAME'
+/
+spool off
+EOSQL
+echo "@/tmp/drop_table_partitions.sql" | sqlplus / as sysdba
+</code>
+  *  drop tables
+<code>
+sqlplus / as sysdba<<EOSQL
+select 'drop table '||table_owner||'.'||table_name||';'
+from   dba_tab_partitions
+where  tablespace_name = '$TABLESPACE_NAME'
+spool /tmp/drop_tables.sql
+/
+spool off
+EOSQL
+echo "@/tmp/drop_tables.sql" | sqlplus / as sysdba
+</code>
+  *  drop tablespace
+<code>
+drop tablespace ads_archive including contents and datafiles
+/
+</code>
+=== Find all table partitions in tablespaces with missing datafiles ===
+<code>
+set lines 1000 pages 100
+col owner for a10
+col segment_name for a40
+col segment_type for a25
+col partition_name for a20
+col tablespace_name for a25
+select owner
+,      segment_name
+,      segment_type
+,      partition_name
+,      tablespace_name
+from   dba_segments
+where  segment_type in ('TABLE PARTITION', 'TABLE SUBPARTITION')
+and    tablespace_name in (
+                          select tablespace_name
+                          from   dba_data_files
+                          where  file_name like '%MISSING%'
+                          )
+order  by 1, 2, decode (segment_type, 'TABLE SUBPARTITION', 1, 2),4
+</code>
+=== Use exchange partition to alter metadata of the tablespaces without moving the actual data ===
+  *  [[https://community.oracle.com/message/2166242#2166242|this]] is an explanation of what you are looking for!\\
 This is a way of doing the exchange with a neat PL/SQL procedure. It might not do what you want though so understand it before running it!
-<code>73@@</code>
+<code>
+set serveroutput on size unlimited
+DECLARE
+  dummy_table                   VARCHAR2 (128);
+  cmd                           VARCHAR2 (2048);
+  currowner                     dba_part_tables.owner%TYPE;
+  currtable                     dba_part_tables.table_name%TYPE;
+  l_subpartitioning_type        dba_part_tables.subpartitioning_type%TYPE;
+  l_def_subpartition_count      dba_part_tables.def_subpartition_count%TYPE;
+  l_subpartitioning_key_count   dba_part_tables.subpartitioning_key_count%TYPE;
+  l_curr_qual_table             VARCHAR2 (128);
-====Workaround====
+  PROCEDURE execsql (p_sql IN VARCHAR2)
+  IS
+  BEGIN
+    DBMS_OUTPUT.put_line (p_sql);
+    EXECUTE IMMEDIATE p_sql;
+  END;
+  PROCEDURE force_drop_table (p_table_name IN VARCHAR2)
+  IS
+  BEGIN
+    execsql ('drop table ' || p_table_name || ' purge');
+  EXCEPTION
+    WHEN OTHERS
+    THEN
+      NULL;
+  END;
+  PROCEDURE exchange_partition (p_from_table       IN VARCHAR2,
+                                p_from_partition   IN VARCHAR2,
+                                p_to_table         IN VARCHAR2)
+  IS
+  BEGIN
+    execsql (
+         'alter table '
+      || p_from_table
+      || ' exchange partition '
+      || p_from_partition
+      || ' with table '
+      || p_to_table
+      || ' excluding indexes');
+  END;
+  PROCEDURE drop_partition (p_table IN VARCHAR2, p_partition IN VARCHAR2)
+  IS
+  BEGIN
+    execsql ('alter table ' || p_table || ' drop partition ' || p_partition);
+  END;
+BEGIN
+  currowner := '.';
+  currtable := '.';
+  FOR asegment
+    IN (  SELECT owner,
+                 segment_name,
+                 segment_type,
+                 partition_name
+            FROM dba_segments
+           WHERE     segment_type IN ('TABLE PARTITION', 'TABLE SUBPARTITION')
+                 AND tablespace_name IN (SELECT tablespace_name
+                                           FROM dba_data_files
+                                          WHERE file_name LIKE '%MISSING%')
+        ORDER BY 1,
+,
+                 DECODE (segment_type, 'TABLE SUBPARTITION', 1, 2),
+)
+  LOOP
+    BEGIN
+      IF currowner <> asegment.owner OR currtable <> asegment.segment_name
+      THEN
+        currowner := asegment.owner;
+        currtable := asegment.segment_name;
+        l_curr_qual_table := asegment.owner || '.' || asegment.segment_name;
+        dummy_table := asegment.owner || '.TBR_EXCHANGE_DUMMY';
+        force_drop_table (dummy_table);
+        IF asegment.segment_type = 'TABLE SUBPARTITION'
+        THEN
+          cmd := 'create table ' || dummy_table || ' partition by ';
+          SELECT subpartitioning_type,
+                 def_subpartition_count,
+                 subpartitioning_key_count
+            INTO l_subpartitioning_type,
+                 l_def_subpartition_count,
+                 l_subpartitioning_key_count
+            FROM dba_part_tables
+           WHERE     owner = asegment.owner
+                 AND table_name = asegment.segment_name;
+          cmd := cmd || l_subpartitioning_type || '(';
+          FOR asubpartkey
+            IN (  SELECT column_position, column_name
+                    FROM dba_subpart_key_columns
+                   WHERE     owner = asegment.owner
+                         AND name = asegment.segment_name
+                         AND object_type = 'TABLE'
+                ORDER BY column_position)
+          LOOP
+            IF asubpartkey.column_position > 1
+            THEN
+              cmd := cmd || ',';
+            END IF;
+            cmd := cmd || asubpartkey.column_name;
+          END LOOP;
+          cmd := cmd || ') partitions (';
+          FOR partnr IN 1 .. l_def_subpartition_count
+          LOOP
+            IF partnr > 1
+            THEN
+              cmd := cmd || ',';
+            END IF;
+            cmd := cmd || 'partition p' || partnr;
+          END LOOP;
+          cmd : ======
+            cmd || ') as select * from ' || l_curr_qual_table || ' where 1=0';
+          execsql (cmd);
+        ELSE
+          execsql (
+               'create table '
+            || dummy_table
+            || ' as select * from '
+            || l_curr_qual_table
+            || ' where 1=0');
+        END IF;
+      END IF;
+      exchange_partition (l_curr_qual_table,
+                          asegment.partition_name,
+                          dummy_table);
+      drop_partition (l_curr_qual_table, asegment.partition_name);
+    EXCEPTION
+      WHEN OTHERS
+      THEN
+        DBMS_OUTPUT.put_line (
+          asegment.owner || '.' || asegment.segment_name || ' ' || SQLERRM);
+    END;
+  END LOOP;
+  IF dummy_table IS NOT NULL
+  THEN
+    force_drop_table (dummy_table);
+  END IF;
+  FOR atablespace IN (  SELECT DISTINCT tablespace_name
+                          FROM dba_data_files
+                         WHERE file_name LIKE '%MISSING%'
+                      ORDER BY 1)
+  LOOP
+    execsql (
+         'drop tablespace '
+      || atablespace.tablespace_name
+      || ' including contents and datafiles');
+  END LOOP;
+END;
+/
+</code>
+=== Workaround ===
 While working on the solution, a backup can be made using the "skip inaccessible" option. Put this in an rman cmdfile...
-<code>74@@</code>
+<code>
+connect catalog rmanusr/rmanpwd@rmancat
+connect target sys/syspwd@adsa1
+run {
+allocate channel t1 type 'sbt_tape' parms 'ENV=(NSR_SERVER=hn6000.company.be,NSR_CLIENT=hn491,NSR_DATA_VOLUME_POOL=DD1DAILY,NSR_END_ERROR_IGNORE=TRUE)';
+allocate channel t2 type 'sbt_tape' parms 'ENV=(NSR_SERVER=hn6000.company.be,NSR_CLIENT=hn491,NSR_DATA_VOLUME_POOL=DD1DAILY,NSR_END_ERROR_IGNORE=TRUE)';
+configure retention policy to recovery window of 28 days;
+configure controlfile autobackup on;
+crosscheck archivelog all;
+SQL 'ALTER SYSTEM SWITCH LOGFILE';
+SQL 'CREATE PFILE FROM SPFILE';
+backup filesperset 1 format 'dbfull_%d_%I_%t_%s_%p' full database plus archivelog skip inaccessible delete all input;
+backup current controlfile;
+release channel t1;
+release channel t2;
+}
+exit
+</code>
-====Another way of deleting the offending objects====
+=== Another way of deleting the offending objects ===
-Taken from [[http://www.dbarj.com.br/en/2015/01/resume-oracle-duplicate-database-failed-recovery-datafiles/|Recover from a failed RMAN duplicate]]<br />
+Taken from [[http://www.dbarj.com.br/en/2015/01/resume-oracle-duplicate-database-failed-recovery-datafiles/|Recover from a failed RMAN duplicate]]\\
 Need to check it before running it blindly!
-<code>75@@</code>
+<code>
-=====ORA-28112: failed to execute policy function=====
+set echo off feed off termo off trims on pages 0 newpa none lines 2000 headi off long 32000
+col val for a1000
+spool destroy_offending_objects.sql
+select to_clob ('alter table '|| table_name||' drop constraint '||constraint_name||';') Val
+from user_constraints where constraint_type in ('R','P') and table_name in (select distinct table_name from user_tab_partitions)
+union all
+select to_clob ('drop index '||index_name||';') Val from user_indexes where table_name in (select distinct table_name from user_tab_partitions)
+union all
+select to_clob ('alter table '||table_name|| ' drop partition '||partition_name||';') from dba_tab_partitions
+where tablespace_name in (select tablespace_name from dba_data_files where file_name like '%MISSING%')
+union all
+Select case
+when instr (VAl, 'PARTITION') = 0 Then
+substr (Val,1,InStr (Val,')')+1)||';'
+else
+substr (Val,1,InStr (Val,')')+1)||' local;'
+End val
+from
+(select dbms_metadata.get_ddl ('INDEX', index_name,'TEST') Val from user_indexes where table_name in (select distinct table_name from user_tab_partitions))
+union all
+Select Val
+from
+(select dbms_metadata.get_dependent_ddl ('REF_CONSTRAINT', table_name,'TEST') Val
+from user_tables where table_name in (select distinct table_name from user_tab_partitions join user_constraints using ( table_name)
+where constraint_type='R'))
+union all
+select Val
+from
+(select dbms_metadata.get_ddl ('CONSTRAINT', constraint_name,'TEST') Val
+from user_constraints
+where constraint_type='P' and table_name in (select distinct table_name from user_tab_partitions)
+)
+union all
+select to_clob (val)
+from (select distinct 'drop tablespace '||tablespace_name|| ' including contents and datafiles;' val
+from dba_tab_partitions
+where tablespace_name in (select tablespace_name from dba_data_files where file_name like '%MISSING%')
+);
+spoo off
+@destroy_offending_objects
+</code>
+==== ORA-28112: failed to execute policy function ====
 when exporting or importing using [[Data Pump]]< /br>
 During expdp is accompanied by "**ORA-31693: Table data object "SYSMAN"."MGMT_IP_REPORT_DEF" failed to load/unload and is being skipped due to error:**"
-=====Reason=====
+==== Reason ====
 This happens due to audit policies being defined on the table but the policy function is defined in another schema.
-=====Solution=====
+==== Solution ====
 Either drop the policy after importing the table with:
-<code>76@@</code>
+<code>
+begin
+    for rec in (select * from dba_audit_policies where object_schema = 'SYSMAN') loop
+        dbms_fga.drop_policy(object_schema => rec.object_schema,
+                             object_name => rec.object_name,
+                             policy_name => rec.policy_name);
+    end loop;
+end;
+/
+</code>
 or exclude the policies during export by adding this to the .par file:
-<code>77@@</code>
+<code>
+exclude=FGA_POLICY
+</code>
 or even better, give the necessary grants to the exporting user...
-<code>78@@</code>
+<code>
+grant exempt access policy to impexpusr;
+</code>
 The sysdba role has this automatically.
-=====ORA-17629: Cannot connect to the remote database server=====
+==== ORA-17629: Cannot connect to the remote database server ====
-Active duplication fails to connect even though dns connection exists in tnsnames.ora<br />
+Active duplication fails to connect even though dns connection exists in tnsnames.ora\\
-<br />
 Problem:
-<code>79@@</code>
+<code>
-<br />
+DUPLICATE TARGET DATABASE FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;
-Solution:<br />
-<br />
+RMAN-03002: failure of Duplicate Db command at 05/27/2015 12:52:24
+RMAN-05501: aborting duplication of target database
+RMAN-03015: error occurred in stored script Memory Script
+RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/27/2015 12:52:23
+ORA-17629: Cannot connect to the remote database server
+ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
+ORA-17629: Cannot connect to the remote database server
+</code>
+Solution:\\
 Update the tnsnames.ora of both destination **and** source ORACLE_HOMEs!
-=====ORA-01950: no privileges on tablespace 'SYSAUX'=====
+==== ORA-01950: no privileges on tablespace 'SYSAUX' ====
-<code>80@@</code>
+<code>
-**Cause:**<br />
+-JUN-15 03:12:27.545: >>> ORA-31642: the following SQL statement fails:
-In this case, it seems SYSMAN has no rights to create objects in the SYSAUX tablespace<br />
+BEGIN "SYS"."DBMS_RULE_EXP_RULES".SCHEMA_CALLOUT(:1,0,1,'12.01.00.02.00'); END;
-**Solution:**<br />
+ORA-01950: no privileges on tablespace 'SYSAUX'
-<code>81@@</code>
+</code>
+**Cause:**\\
+In this case, it seems SYSMAN has no rights to create objects in the SYSAUX tablespace\\
+**Solution:**\\
+<code>
+alter user SYSMAN quota unlimited on sysaux;
+</code>
 More information [[https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=bov7jp5pq_4&_afrLoop=547371065896716#SYMPTOM|here]]
-=====ORA-39080: failed to create queues "KUPC$C_1_20150616165037" and "KUPC$S_1_20150616165037" for Data Pump job=====
+==== ORA-39080: failed to create queues "KUPC$C_1_20150616165037" and "KUPC$S_1_20150616165037" for Data Pump job ====
-<code>82@@</code>
+<code>
-====Cause:====
+expdp /
+Export: Release 11.2.0.4.0 - Production on Tue Jun 16 16:50:36 2015
+Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
+Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
+With the Partitioning, OLAP, Data Mining and Real Application Testing options
+ORA-31626: job does not exist
+ORA-31637: cannot create job SYS_EXPORT_SCHEMA_01 for user OPS$ORAIBM
+ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
+ORA-06512: at "SYS.KUPV$FT_INT", line 798
+[[ORA-39080: failed to create queues "KUPC$C_1_20150616165037" and "KUPC$S_1_20150616165037" for Data Pump job]]
+ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
+ORA-06512: at "SYS.KUPC$QUE_INT", line 1534
+ORA-24002: QUEUE_TABLE SYS.AQ$KUPC$DATAPUMP_QUETAB_1 does not exist
+</code>
+=== Cause: ===
 Possibly some or all of OLAP module has got itself deinstalled.
-====Solution:====
+=== Solution: ===
 Try:
-  * Recompiling invalid objects. @?/rdbms/admin/utlrp
+  *  Recompiling invalid objects. @?/rdbms/admin/utlrp
-  * Reinstalling DBMS_CUBE packages by (re)running catproc.sql. @?/rdbms/admin/catproc followed by @?/rdbms/admin/utlrp
+  *  Reinstalling DBMS_CUBE packages by (re)running catproc.sql. @?/rdbms/admin/catproc followed by @?/rdbms/admin/utlrp
-  * Reinstalling OLAP. @?/olap/admin/olap.sql SYSAUX TEMP; But see Metalink note ID 296187.1 for full instructions.
+  *  Reinstalling OLAP. @?/olap/admin/olap.sql SYSAUX TEMP; But see Metalink note ID 296187.1 for full instructions.
-  * Check metalink for bugs. See ID 345198.1 and possibly ID 453796.1
+  *  Check metalink for bugs. See ID 345198.1 and possibly ID 453796.1
-=====ORA-14063: Unusable index exists on unique/primary constraint key=====
+==== ORA-14063: Unusable index exists on unique/primary constraint key ====
-**ORA-39083: Object type CONSTRAINT failed to create with error:**<br />
+**ORA-39083: Object type CONSTRAINT failed to create with error:**\\
-During an import datapump (impdp), import failed because indexes were in error before the tables were exported. This causes the import of rows to be skipped.<br />
+During an import datapump (impdp), import failed because indexes were in error before the tables were exported. This causes the import of rows to be skipped.\\
 If the export cannot be done again (having compiled the indexes), then the metadata can be imported and the indexes compiled before importing the data.
-  * Import the metadata
+  *  Import the metadata
-<code>83@@</code>
+<code>
-  * Find the bad indexes and recompile them
+cat<<EOCAT >impdp_adst_ads_archive_metadata.par
-<code>84@@</code>
+userid='/ as sysdba'
-  * Import the data
+dumpfile=adst_ads_archive.dmp
-<code>85@@</code>
+logfile=impdp_adst_ads_archive_metadata.log
+content=metadata_only
+EOCAT
-=====ORA-09925: Unable to create audit trail file=====
+impdp parfile=impdp_adst_ads_archive_metadata.par
-<code>86@@</code>
+</code>
+  *  Find the bad indexes and recompile them
+<code>
+select 'alter index '|| owner||'.'||index_name||' rebuild online parallel 8;'
+from   dba_indexes
+where  status = 'UNUSABLE'
+/
+</code>
+  *  Import the data
+<code>
+cat<<EOCAT >impdp_adst_ads_archive_data.par
+userid='/ as sysdba'
+dumpfile=adst_ads_archive.dmp
+logfile=impdp_adst_ads_archive_data.log
+table_exists_action=append
+content=data_only
+EOCAT
+impdp parfile=impdp_adst_ads_archive_data.par
+</code>
+==== ORA-09925: Unable to create audit trail file ====
+<code>
+SQL> startup nomount pfile="/oracle/TRSCRP1/admin/initTRSCRP1.ora"
+ORA-09925: Unable to create audit trail file
+SVR4 Error: 2: No such file or directory
+Additional information: 9925
+</code>
 or
-<code>87@@</code>
+<code>
-====Problem====
+connected to auxiliary database (not started)
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-03002: failure of Duplicate Db command at 08/29/2017 15:53:09
+RMAN-05501: aborting duplication of target database
+RMAN-03015: error occurred in stored script Memory Script
+RMAN-04014: startup failed: ORA-09925: Unable to create audit trail file
+IBM AIX RISC System/6000 Error: 2: No such file or directory
+Additional information: 9925
+Recovery Manager complete.
+</code>
+=== Problem ===
 This will be almost certainly one of two issues. Either the directory is not writable by the id that started the instance or the directory just does not exist.
-====Solution====
+=== Solution ===
 Note there can be a difference between what you see when you type:
-<code>88@@</code>
+<code>
+show parameter audit
+</code>
 and when you type:
-<code>89@@</code>
+<code>
+strings -a $ORACLE_HOME/dbs/spfile${ORACLE_SID}.ora | grep -i audit
+</code>
 due to someone doing an "alter system" without the "scope=spfile" or "scope=both"
-<code>90@@</code>
+<code>
-=====ORA-09925: Unable to create audit trail file=====
+SQL> host ls -al /oracle/TRSCRP1/admin/adump
+SQL> host mkdir /oracle/TRSCRP1/admin/adump
+</code>
+==== ORA-03113: end-of-file on communication channel ====
+**Problem**\\
+After a shutdown abort and a startup, got nasty looking errors. Shutdown and restart, same error, different codes!
+<code>
+SQL*Plus: Release 12.2.0.1.0 Production on Thu Jun 20 19:26:32 2019
+Copyright (c) 1982, 2016, Oracle.  All rights reserved.
+Connected to an idle instance.
+SQL> startup
+ORACLE instance started.
+Total System Global Area 2097152000 bytes
+Fixed Size                  8622624 bytes
+Variable Size             788532704 bytes
+Database Buffers         1291845632 bytes
+Redo Buffers                8151040 bytes
+Database mounted.
+ORA-03113: end-of-file on communication channel
+Process ID: 3250
+Session ID: 9 Serial number: 63490
+</code>
+**Solution**\\
+Clear any unarchived redo and try again
+<code>
+alter database clear unarchived logfile group 1;
+alter database clear unarchived logfile group 2;
+alter database clear unarchived logfile group 3;
+shu immediate
+startup
+ORACLE instance started.
+Total System Global Area 2097152000 bytes
+Fixed Size                  8622624 bytes
+Variable Size             788532704 bytes
+Database Buffers         1291845632 bytes
+Redo Buffers                8151040 bytes
+Database mounted.
+Database Opened.
+SQL>
+</code>
+==== ORA-09925: Unable to create audit trail file ====
 When starting SQL*Plus with no db processes running (expecting to see "Connected to an idle instance"), we get this:
-<code>91@@</code>
+<code>
-  * Filesystem permissions are fine
+ERROR:
-  * 'strings spfile<SID>.ora | grep audit' shows the correct directory
+ORA-09925: Unable to create audit trail file
-  * Running on AIX
+IBM AIX RISC System/6000 Error: 2: No such file or directory
+Additional information: 9925
+ORA-01075: you are currently logged on
+</code>
+  *  Filesystem permissions are fine
+  *  'strings spfile<SID>.ora | grep audit' shows the correct directory
+  *  Running on AIX
 AIX is the clue here... need to check for semaphores and locked shared memory objects
-<code>92@@</code>
+<code>
+(0) UCLID55D oraibm@solax082:/oracle/product/11.2.0.3/bin> sysresv
-=====RMAN-06023: no backup or copy of datafile 1 found to restore=====
+IPC Resources for ORACLE_SID "UCLID55D" :
-<code>93@@</code>
+Shared Memory:
-Seems pretty straightforward? SCN was set too early for the retention period. But no, not this time.<br />
+ID              KEY
-<code>94@@</code>
+8388640         0xffffffff
-SCN was only 12 hours ago with a retention of 28 days! The backup piece dated 2018-05-15 01:06:22 is clearly available and on the device type set_tape.<br />
+579862561       0xffffffff
+384827426       0x326f32e8
+Semaphores:
+ID              KEY
+167772287       0xf09f4914
+Oracle Instance alive for sid "UCLID55D"
+(0) UCLID55D oraibm@solax082:/oracle/product/11.2.0.3/bin> ipcrm -m 8388640
+(0) UCLID55D oraibm@solax082:/oracle/product/11.2.0.3/bin> ipcrm -m 579862561
+(0) UCLID55D oraibm@solax082:/oracle/product/11.2.0.3/bin> ipcrm -m 384827426
+(0) UCLID55D oraibm@solax082:/oracle/product/11.2.0.3/bin> ipcrm -s 167772287
+(0) UCLID55D oraibm@solax082:/oracle/product/11.2.0.3/bin> sysresv
+IPC Resources for ORACLE_SID "UCLID55D" :
+Shared Memory
+ID              KEY
+No shared memory segments used
+Semaphores:
+ID              KEY
+No semaphore resources used
+Oracle Instance not alive for sid "UCLID55D"
+(0) UCLID55D oraibm@solax082:/oracle/product/11.2.0.3/bin> ps -ef | grep UCLID55D
+oraibm 3326 23776 0 14:12:05 pts/1 0:00 grep UCLID55D
+</code>
+==== RMAN-06457: UNTIL SCN (325670056508) is ahead of last SCN in archived logs (325670045001) ====
+During RMAN duplicate this error meant that the clone could not go ahead.
+Even though all the logs had been archived and backed up with Commvault, the error still showed up.\\
+It turns out Commvault was not setup to sync the catalog (in advanced options tab).\\
+I found it when I went to RMAN via the command line and did a 'list archivelog all'. Before starting to list everything, it did a resync which took an age. This was the clue.
+==== RMAN-06023: no backup or copy of datafile 1 found to restore ====
+<code>
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-03002: failure of Duplicate Db command at 05/16/2018 16:08:13
+RMAN-05501: aborting duplication of target database
+RMAN-05556: not all datafiles have backups that can be recovered to SCN 278250901080
+RMAN-03015: error occurred in stored script Memory Script
+RMAN-06026: some targets not found - aborting restore
+RMAN-06023: no backup or copy of datafile 7 found to restore
+RMAN-06023: no backup or copy of datafile 6 found to restore
+RMAN-06023: no backup or copy of datafile 5 found to restore
+RMAN-06023: no backup or copy of datafile 4 found to restore
+RMAN-06023: no backup or copy of datafile 3 found to restore
+RMAN-06023: no backup or copy of datafile 2 found to restore
+RMAN-06023: no backup or copy of datafile 1 found to restore
+</code>
+Seems pretty straightforward? SCN was set too early for the retention period. But no, not this time.\\
+<code>
+RMAN> list backup of datafile 1;
+List of Backup Sets
+===================
+BS Key  Type LV Size       Device Type Elapsed Time Completion Time
+------- ---- -- ---------- ----------- ------------ -------------------
+110170340 Incr 0  132.90G    SBT_TAPE    00:18:32     2018-05-14 01:19:40
+        BP Key: 110170345   Status: AVAILABLE  Compressed: NO  Tag: WEEKLY_FULL
+        Handle: 24639762_REPORTA_07t2r3uk_1_1   Media: V_17152809_77191330
+  List of Datafiles in backup set 110170340
+  File LV Type Ckp SCN    Ckp Time            Name
+  ---- -- ---- ---------- ------------------- ----
+    0  Incr 277998128991 2018-05-14 01:01:08 /cln/acc/ora_data3/reporta/system01.dbf
+BS Key  Type LV Size       Device Type Elapsed Time Completion Time
+------- ---- -- ---------- ----------- ------------ -------------------
+110355808 Incr 1  204.00M    SBT_TAPE    00:05:13     2018-05-15 01:06:22
+        BP Key: 110355813   Status: AVAILABLE  Compressed: NO  Tag: DAILY_INCREMENTAL
+        Handle: 24661053_REPORTA_3ft2toal_1_1   Media: V_17173010_77253424
+  List of Datafiles in backup set 110355808
+  File LV Type Ckp SCN    Ckp Time            Name
+  ---- -- ---- ---------- ------------------- ----
+    1  Incr 278044267395 2018-05-15 01:01:09 /cln/acc/ora_data3/reporta/system01.dbf
+BS Key  Type LV Size       Device Type Elapsed Time Completion Time
+------- ---- -- ---------- ----------- ------------ -------------------
+110543802 Incr 1  237.00M    SBT_TAPE    00:05:51     2018-05-16 01:07:40
+        BP Key: 110543807   Status: AVAILABLE  Compressed: NO  Tag: DAILY_INCREMENTAL
+        Handle: 24681602_REPORTA_6nt30cnt_1_1   Media: V_17133646_77315270
+  List of Datafiles in backup set 110543802
+  File LV Type Ckp SCN    Ckp Time            Name
+  ---- -- ---- ---------- ------------------- ----
+    1  Incr 278286445856 2018-05-16 01:01:50 /cln/acc/ora_data3/reporta/system01.dbf
+</code>
+SCN was only 12 hours ago with a retention of 28 days! The backup piece dated 2018-05-15 01:06:22 is clearly available and on the device type set_tape.\\
 The trick here was to look back through the logs and look more closely at what the file restore message was saying...
-<code>95@@</code>
+<code>
+channel t0: starting datafile backup set restore
+channel t0: specifying datafile(s) to restore from backup set
+channel t0: restoring datafile 00001 to /cln/acc/ora_data3/reporta/system01.dbf             < < < ==============
+channel t0: reading from backup piece dbfull_REPORTA_738887052_967580186_116_1
+channel t2: ORA-19870: error while restoring backup piece dbfull_REPORTA_738887052_967579750_113_1
+ORA-19507: failed to retrieve sequential file, handle="dbfull_REPORTA_738887052_967579750_113_1", parms=""
+ORA-27029: skgfrtrv: sbtrestore returned error
+ORA-19511: non RMAN, but media manager or vendor specific failure, error text:
+   CreateOraObject20: Job[[0]] thread[[55116090]]: Restore: GetBackupInfo() for Archive File[[dbfull_REPORTA_738887052_967579750_113_1]] failed.
+ORA-0651
+</code>
 It was not the fact that it could not find the data on tape, it was that it could not save the file to the location indicated!!
-In the RMAN DUPLICATE script, I had all the file paths redirected (a mixture of convert parameters and spfile set) to a /restore filesystem.<br />
+In the RMAN DUPLICATE script, I had all the file paths redirected (a mixture of convert parameters and spfile set) to a /restore filesystem.\\
-But I forgot one!<br />
+But I forgot one!\\
 A handy thing to do is to search out the file paths with this to see if something is still wrong:
-<code>96@@</code>
+<code>
+cd $ORACLE_HOME/dbs
+strings -a spfile${ORACLE_SID}.ora | grep dest
+</code>
 This should show any paths still not getting caught by the DUPLICATE script.
-=====RMAN-06136: ORACLE error from auxiliary database: ORA-06502: PL/SQL: numeric or value error=====
+==== RMAN-06136: ORACLE error from auxiliary database: ORA-06502: PL/SQL: numeric or value error ====
 When cloning a database, the duplicate fails with this error.
-====Problem====
+=== Problem ===
 This duplicate had no target connection but did have a catalog and auxiliary connection.
 In the catalog (which was the catalog where the target was registerd), there was also an entry in that catalog with the same name as the auxiliary - left over from the days when these 2 instances were Primary and Standby in a Dataguard configuration.
-<code>97@@</code>
+<code>
-====Solution====
+...
+Datafile 414 skipped by request
+Datafile 415 skipped by request
+Datafile 419 skipped by request
+Datafile 420 skipped by request
+Datafile 421 skipped by request
+DBGSQL:     AUXILIARY> begin :ofname := sys.dbms_backup_restore.convertFileName( fname   =>   :ifname, ftype   =>   :iftype, osftype =>   TRUE); end;
+DBGSQL:        sqlcode = 6502
+DBGSQL:         B :ofname = NULL
+DBGSQL:         B :ifname  ======
+DBGSQL:         B :iftype = 2
+restarting auxiliary database without server parameter file
+Oracle instance started
+Total System Global Area   32068440064 bytes
+Fixed Size                     2262200 bytes
+Variable Size              16978545480 bytes
+Database Buffers           15032385536 bytes
+Redo Buffers                  55246848 bytes
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-03002: failure of Duplicate Db command at 10/04/2017 14:50:29
+RMAN-05501: aborting duplication of target database
+RMAN-06136: ORACLE error from auxiliary database: ORA-06502: PL/SQL: numeric or value error
+Recovery Manager complete.
+</code>
+=== Solution ===
 Connect to the catalog and unregister the name of the auxiliary database. It shouldn't be there anyway.
-<code>98@@</code>
+<code>
+rman catalog rmanusr/rmanpwd@rmancat
-=====ORA-39181: Only partial table data may be exported due to fine grain access control on "owner"."<table_name>"=====
+Recovery Manager: Release 11.2.0.4.0 - Production on Wed Oct 4 15:00:00 2017
-=====Reason=====
+Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
+connected to recovery catalog database
+RMAN> set dbid 4260753020;
+executing command: SET DBID
+database name is "LBK" and DBID is 4260753020
+RMAN> list db_unique_name of database;
+List of Databases
+DB Key  DB Name  DB ID            Database Role    Db_unique_name
+------- ------- ----------------- ---------------  ------------------
+13301587 LBK      4260753020       PRIMARY          LBK
+13301587 LBK      4260753020       STANDBY          ACC_PERF
+13301587 LBK      4260753020       STANDBY          LBKRO
+RMAN> unregister db_unique_name 'ACC_PERF';
+database db_unique_name is "ACC_PERF", db_name is "LBK" and DBID is 4260753020
+Want to unregister the database with target db_unique_name (enter YES or NO)? YES
+database with db_unique_name ACC_PERF unregistered from the recovery catalog
+RMAN> exit
+</code>
+==== ORA-39181: Only partial table data may be exported due to fine grain access control on "owner"."<table_name>" ====
+==== Reason ====
 This happens when applications are designed with fine-grained access.
-=====Solution=====
+==== Solution ====
 Grant the necessary privileges to the exporting user...
-<code>99@@</code>
+<code>
+grant exempt access policy to ops$oraibm;
+</code>
-=====ORA-04030: out of process memory when trying to allocate 2520 bytes=====
+==== ORA-40238: invalid linear algebra shared library ====
-<code>100@@</code>
+During installation of Oracle software 18c, errors are received. This is because the 18c installer uses Fortran Runtime libraries not always installed.
+<code>
+ORA-40238: invalid linear algebra shared library
+/cln/acc/ora_bin1/app/oracle/product/18.4/dbhome_1/lib/libora_netlib.so
+</code>
+Check for the presence of the necessary libraries using
+<code>
+lslpp -L all | grep -i "Fortran Runtime"
+</code>
-=====ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select name,online$,contents...","KGLH0^e5705bfc","kglHeapInitialize:temp")=====
+==== ORA-04030: out of process memory when trying to allocate 2520 bytes ====
-  * [[https://blogs.oracle.com/db/entry/ora-4031_troubleshooting|ORA-4031 Troubleshooting]]
+<code>
-  * [[https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=89215376348478&id=396940.1&_afrWindowMode=0&_adf.ctrl-state=u1z0xcppg_4|Troubleshooting and Diagnosing ORA-4031 Error - (Doc ID 396940.1)]]
+expdp problem (ORA-04030: out of process memory when trying to allocate 2520 bytes (pga heap,koh-kghu call heap))
-<code>101@@</code>
-Database had already crashed so nothing to do but:<br />
+select value from v$pgastat where name='maximum PGA allocated';
-<code>102@@</code>
-but for reference, the causes could well be the applications running on the database are not using bind variables...<br />
+SHOW PARAMETER TARGET
+ORA-04030: out of process memory when trying to allocate 2520 bytes (pga heap,koh-kghu call heap)
+alter system reset pga_aggregate_target scope=spfile;
+show sga
+select * from v$pgastat;
+show parameter process
+select sum(value)/1024/1024 Mb
+from   v$sesstat s, v$statname n
+where  1=1
+and    n.STATISTIC# = s.STATISTIC#
+and    name = 'session pga memory'
+show parameter WORKAREA
+select value from v$pgastat where name='maximum PGA allocated';
+alter system set memory_max_target=10G scope=spfile;
+alter system set memory_target=3G scope=both;
+select * from v$memory_target_advice order by memory_size;
+</code>
+==== ORA-445 Background Process "m000" Did Not Start After 60 Seconds ====
+  *  [[https://community.oracle.com/docs/DOC-1006799|ORA-00445 issues explained]]
+Extended the default timeout on the listener by adding
+<code>
+sqlnet.inbound_connection_timeout=180
+</code>
+to the sqlnet.ora
+<code>
+inbound_connection_timeout=120
+</code>
+and to the listener.ora
+==== opidrv aborting process J002 ospid (1573990) as a result of ORA-1013 ====
+This means that a job has been automatically cancelled because it overran the maintenance window. Check what the values are at the moment.
+<code>
+col window_name     for a25
+col resource_plan   for a30
+col repeat_interval for a80
+col duration        for a15
+select window_name,resource_plan, repeat_interval, duration from dba_scheduler_windows;
+</code>
+This gives us something like this...
+<code>
+WINDOW_NAME                    RESOURCE_PLAN                  REPEAT_INTERVAL                                                                  DURATION
+------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------------------
+MONDAY_WINDOW                  DEFAULT_MAINTENANCE_PLAN       freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0                            +000 04:00:00
+TUESDAY_WINDOW                 DEFAULT_MAINTENANCE_PLAN       freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0                            +000 04:00:00
+WEDNESDAY_WINDOW               DEFAULT_MAINTENANCE_PLAN       freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0                            +000 04:00:00
+THURSDAY_WINDOW                DEFAULT_MAINTENANCE_PLAN       freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0                            +000 04:00:00
+FRIDAY_WINDOW                  DEFAULT_MAINTENANCE_PLAN       freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0                            +000 04:00:00
+SATURDAY_WINDOW                DEFAULT_MAINTENANCE_PLAN       freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0                             +000 20:00:00
+SUNDAY_WINDOW                  DEFAULT_MAINTENANCE_PLAN       freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0                             +000 20:00:00
+WEEKNIGHT_WINDOW                                              freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0            +000 08:00:00
+WEEKEND_WINDOW                                                freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0                              +002 00:00:00
+rows selected.
+</code>
+The values are set to the defaults. We can extend the night window from 4 hours to 6.
+<code>
+begin
+    dbms_scheduler.disable(name => 'MONDAY_WINDOW');
+    dbms_scheduler.disable(name => 'TUESDAY_WINDOW');
+    dbms_scheduler.disable(name => 'WEDNESDAY_WINDOW');
+    dbms_scheduler.disable(name => 'THURSDAY_WINDOW');
+    dbms_scheduler.disable(name => 'FRIDAY_WINDOW');
+    dbms_scheduler.set_attribute ( name      => 'MONDAY_WINDOW',
+                                   attribute => 'DURATION',
+                                   value     => numtodsinterval(6, 'hour')
+                                 );
+    dbms_scheduler.set_attribute ( name      => 'TUESDAY_WINDOW',
+                                   attribute => 'DURATION',
+                                   value     => numtodsinterval(6, 'hour')
+                                 );
+    dbms_scheduler.set_attribute ( name      => 'WEDNESDAY_WINDOW',
+                                   attribute => 'DURATION',
+                                   value     => numtodsinterval(6, 'hour')
+                                 );
+    dbms_scheduler.set_attribute ( name      => 'THURSDAY_WINDOW',
+                                   attribute => 'DURATION',
+                                   value     => numtodsinterval(6, 'hour')
+                                 );
+    dbms_scheduler.set_attribute ( name      => 'FRIDAY_WINDOW',
+                                   attribute => 'DURATION',
+                                   value     => numtodsinterval(6, 'hour')
+                                 );
+    dbms_scheduler.enable(name => 'MONDAY_WINDOW');
+    dbms_scheduler.enable(name => 'TUESDAY_WINDOW');
+    dbms_scheduler.enable(name => 'WEDNESDAY_WINDOW');
+    dbms_scheduler.enable(name => 'THURSDAY_WINDOW');
+    dbms_scheduler.enable(name => 'FRIDAY_WINDOW');
+end;
+/
+</code>
+And checking the results again shows...
+<code>
+WINDOW_NAME               RESOURCE_PLAN                  REPEAT_INTERVAL                                                                  DURATION
+------------------------- ------------------------------ -------------------------------------------------------------------------------- ---------------
+MONDAY_WINDOW             DEFAULT_MAINTENANCE_PLAN       freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0                            +000 06:00:00
+TUESDAY_WINDOW            DEFAULT_MAINTENANCE_PLAN       freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0                            +000 06:00:00
+WEDNESDAY_WINDOW          DEFAULT_MAINTENANCE_PLAN       freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0                            +000 06:00:00
+THURSDAY_WINDOW           DEFAULT_MAINTENANCE_PLAN       freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0                            +000 06:00:00
+FRIDAY_WINDOW             DEFAULT_MAINTENANCE_PLAN       freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0                            +000 06:00:00
+SATURDAY_WINDOW           DEFAULT_MAINTENANCE_PLAN       freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0                             +000 20:00:00
+SUNDAY_WINDOW             DEFAULT_MAINTENANCE_PLAN       freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0                             +000 20:00:00
+WEEKNIGHT_WINDOW                                         freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0            +000 08:00:00
+WEEKEND_WINDOW                                           freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0                              +002 00:00:00
+rows selected.
+</code>
+==== ORA-04031: Unable To Allocate Bytes Of Shared Memory (“Large Pool”,”Unknown Object”,”Large Pool”,”CTWR Dba Buffer”) ====
+This wait event occurs when block change tracking file reached its maximum size and unable to extend it.\\
+There may be other errors reported in the alertlog
+==== ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select name,online$,contents...","KGLH0^e5705bfc","kglHeapInitialize:temp") ====
+  *  [[https://blogs.oracle.com/db/ora-4031-troubleshooting|Master Notes: ORA-4031 Troubleshooting]]
+  *  [[https://support.oracle.com/epmos/faces/DocumentDisplay?id=559339.1|Diagnostic Tools Catalog (Doc ID 559339.1)]]
+  *  [[https://support.oracle.com/epmos/faces/DocumentDisplay?id=430473.1|ORA-4031 Common Analysis/Diagnostic Scripts [Video] (Doc ID 430473.1)]]
+  *  [[https://support.oracle.com/epmos/faces/DocumentDisplay?id=396940.1|Troubleshooting and Diagnosing ORA-4031 Error - (Doc ID 396940.1)]]
+  *  [[https://support.oracle.com/epmos/faces/DocumentDisplay?id=430473.1#aref_section51|General Analysis of the Shared Pool and ORA-4031 Problems]]
+<code>
+ORA-04031: unable to allocate  bytes of shared memory ("","","","")
+ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select name,online$,contents...","KGLH0^e5705bfc","kglHeapInitialize:temp")
+Incident details in: /oracle/diag/rdbms/mape/MAPE/incident/incdir_86754/MAPE_dm00_14221536_i86754.trc
+Wed Jul 31 20:42:50 2013
+Dumping diagnostic data in directory=[[cdmp_20130731204250]], requested by (instance=1, osid=14221536 (DM00)), summary=[[incident=86753]].
+Wed Jul 31 20:42:51 2013
+Sweep [[inc]][[86754]]: completed
+Sweep [[inc]][[86753]]: completed
+Sweep [[inc2]][[86753]]: completed
+Wed Jul 31 20:42:57 2013
+Use ADRCI or Support Workbench to package the incident.
+See Note 411.1 at My Oracle Support for error and packaging details.
+Dumping diagnostic data in directory=[[cdmp_20130731204257]], requested by (instance=1, osid=14221536 (DM00)), summary=[[incident=86754]].
+Wed Jul 31 20:43:35 2013
+Errors in file /oracle/diag/rdbms/mape/MAPE/trace/MAPE_dw02_40567024.trc  (incident=82993):
+ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select order#,columns,types ...","KGLH0^470434f8","kglHeapInitialize:temp")
+</code>
+Database had already crashed so nothing to do but:\\
+<code>
+(0) MAPE ora@x025:/home/ora> sqlplus / as sysdba
+SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 2 14:26:20 2013
+Copyright (c) 1982, 2011, Oracle.  All rights reserved.
+Connected.
+SYS@MAPE> alter system flush shared_pool;
+alter system flush shared_pool
+  *
+ERROR at line 1:
+ORA-01012: not logged on
+Process ID: 0
+Session ID: 0 Serial number: 0
+SYS@MAPE> shutdown abort
+ORACLE instance shut down.
+SYS@MAPE> startup
+ORACLE instance started.
+Total System Global Area  734892032 bytes
+Fixed Size                  2225128 bytes
+Variable Size             398461976 bytes
+Database Buffers          318767104 bytes
+Redo Buffers               15437824 bytes
+Database mounted.
+Database opened.
+SYS@MAPE> show parameter pool
+NAME                                 TYPE                           VALUE
+------------------------------------ ------------------------------ ------------------------------
+buffer_pool_keep                     string
+buffer_pool_recycle                  string
+global_context_pool_size             string
+java_pool_size                       big integer                    0
+large_pool_size                      big integer                    0
+olap_page_pool_size                  big integer                    0
+shared_pool_reserved_size            big integer                    18M
+shared_pool_size                     big integer                    208M
+streams_pool_size                    big integer                    0
+SYS@MAPE> show sga
+Total System Global Area  734892032 bytes
+Fixed Size                  2225128 bytes
+Variable Size             385879064 bytes
+Database Buffers          331350016 bytes
+Redo Buffers               15437824 bytes
+SYS@MAPE> exit
+</code>
+but for reference, the causes could well be the applications running on the database are not using bind variables...\\
 From [[http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:528893984337|asktom.oracle.com]]
-<code>103@@</code>
+<code>
+...
+Just to give you a tiny idea of how huge of a difference this
+can make performance wise, you only need to run a very small
+test:
+tkyte@TKYTE816> alter system flush shared_pool;
+System altered.
+tkyte@TKYTE816> declare
+      type rc is ref cursor;
+      l_rc rc;
+      l_dummy all_objects.object_name%type;
+      l_start number default dbms_utility.get_time;
+  begin
+      for i in 1 .. 1000
+      loop
+          open l_rc for
+          'select object_name
+             from all_objects
+            where object_id = ' || i;
+          fetch l_rc into l_dummy;
+          close l_rc;
+      end loop;
+      dbms_output.put_line
+      ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
+        ' seconds...' );
+  end;
+  /
+.86 seconds...
+PL/SQL procedure successfully completed.
+tkyte@TKYTE816> declare
+      type rc is ref cursor;
+      l_rc rc;
+      l_dummy all_objects.object_name%type;
+      l_start number default dbms_utility.get_time;
+  begin
+      for i in 1 .. 1000
+      loop
+          open l_rc for
+          'select object_name
+             from all_objects
+            where object_id = :x'
+          using i;
+          fetch l_rc into l_dummy;
+          close l_rc;
+      end loop;
+      dbms_output.put_line
+      ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
+        ' seconds...' );
+  end;
+  /
+.27 seconds...
+PL/SQL procedure successfully completed.
+That is pretty dramatic.  The fact is that not only does this
+execute much faster (we spent more time PARSING our queries then
+actually EXECUTING them!) it will let more users use your system
+simultaneously.
+...
+</code>
 and from [[http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1163635055580|linked question]]
-<code>104@@</code>
+<code>
-====Advice from Oracle concerning a concrete (12c) example of unable to allocate 52824 bytes of shared memory ("shared pool",...====
+...
+Lbrary cache latch contention is typically caused by NOT using bind variables.  It is due
+to excessive parsing of statements.
+One way to see if this might be the case in your situation is to run a script like:
+create table t1 as select sql_text from v$sqlarea;
+alter table t1 add sql_text_wo_constants varchar2(1000);
+create or replace function
+remove_constants( p_query in varchar2 ) return varchar2
+as
+    l_query long;
+    l_char  varchar2(1);
+    l_in_quotes boolean default FALSE;
+begin
+    for i in 1 .. length( p_query )
+    loop
+        l_char := substr(p_query,i,1);
+        if ( l_char = **' and l_in_quotes )
+        then
+            l_in_quotes := FALSE;
+        elsif ( l_char = **' and NOT l_in_quotes )
+        then
+            l_in_quotes := TRUE;
+            l_query := l_query || **#';
+        end if;
+        if ( NOT l_in_quotes ) then
+            l_query := l_query || l_char;
+        end if;
+    end loop;
+    l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
+    for i in 0 .. 8 loop
+        l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
+        l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
+    end loop;
+    return upper(l_query);
+end;
+/
+update t1 set sql_text_wo_constants = remove_constants(sql_text);
+select sql_text_wo_constants, count(*)
+  from t1
+ group by sql_text_wo_constants
+having count(*) > 100
+ order by 2
+/
+The output of that last query will show you statements that are identical in the shared
+pool after all numbers and character string constants have been removed.  These
+statements -- and more importantly their counts -- are the potential bottlenecks.  In
+addition to causing the contention, they will be HUGE cpu consumers.
+If you discover your applications do not use bind variables -- you must have this
+corrected.  You'll never have a good hit ratio if everyone submits "unique" sql.  Your
+shared pool will never be used right and you'll be using excessive CPU (90% of the time
+it takes to process "insert into t values ( 1 )" is parsing.  If you use "insert into t
+values ( :x )", and bind the value of 1 -- then the next person that runs that insert
+will benefit from your work and run that much faster.
+...
+</code>
+=== Advice from Oracle concerning a concrete (12c) example of unable to allocate 52824 bytes of shared memory ("shared pool",... ===
 This was from a ticket raised for a Data Pump export that consistently failed with:
-<code>105@@</code>
+<code>
-<code>106@@</code>
+Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE
+Processing object type SCHEMA_EXPORT/TABLE/TABLE
+ORA-39014: One or more workers have prematurely exited.
+ORA-39029: worker 1 with process name "DW00" prematurely terminated
+ORA-31671: Worker process DW00 had an unhandled exception.
+ORA-04031: unable to allocate 52824 bytes of shared memory ("shared pool","unknown object","KTSL subheap","ktsl_load_disp-2")
+ORA-06512: at "SYS.KUPW$WORKER", line 2122
+ORA-06512: at line 2
+</code>
+<code>
+The error ORA-4031 on the shared pool can indicate one of two things:
+- insufficient space for the shared pool (so insufficient memory in SGA at that time)
+OR
+- although there is enough memory, it is fragmented and no contiguous chunk can be allocated to satisfy the latest memory request.
-=====ORA-00392: log 1 of thread 1 is being cleared, operation not allowed=====
+Each SGA has a little fragmentation of course because of the operations taking place, so in your case the fragmentation is represented by the 18M of free memory, which exists in uncontiguous chunks.
+This means that the root cause of the error is insufficient contiguous memory to allocate 12312 bytes of shared memory.
+ACTION PLAN
+==============
+. We see memory_target=2000M. This is too little to support a 12c database, even when it has little activity.
+As you may know 1.7G for the SGA was acceptable when speaking of 32bit OSs where it was hardly possible to make use of more than that.
+Since you have a 64 bit system, the addressable memory is virtually unlimited.
+Furthermore, by using AMM (memory_target>0) this memory is divided between the SGA and the PGA (the user processes) which in fact, depending on the number of connections, can limit the SGA even more.
+So the first recommendation is to increase the value of memory_target to 4G for example. There is no ideal value, but this would be a reasonable starting value for tuning.
+You can do further tuning of the memory using the view V$MEMORY_TARGET_ADVICE.
+The view V$MEMORY_TARGET_ADVICE provides tuning advice for the MEMORY_TARGET initialization parameter:
+SQL> SELECT * FROM v$memory_target_advice ORDER BY memory_size;
+The dynamic performance view V$MEMORY_DYNAMIC_COMPONENTS shows the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA.
+You can also use V$MEMORY_RESIZE_OPS which has a circular history buffer of the last 800 memory resize requests.
+. Since the AMM (you have memory_target>0) is used, then make sure to set:
+ _shared_pool_reserved_pct to 10 or 15 to ensure that when the Shared Pool grows or shrinks automatically, the Reserved Area will change as well.
+SQL> alter system set "_shared_pool_reserved_pct"=15 scope=spfile;
+--restart the instance
+FYI: By default, Oracle configures a small Reserved Pool (or Reserved Area) inside the Shared Pool.
+This memory can be used for operations such as PL/SQL and trigger compilation or for temporary space while loading Java objects.
+After the memory allocated from the Reserved Pool is freed, it returns to the Reserved Pool.
+% of the Shared Pool is set aside as the Reserved Pool to handle allocations of memory higher than defined by the hidden parameter _shared_pool_reserved_pct.
+In some application environments, 5% is too small.
+. Set shared_pool_size=500M or even more. This will represent a minimum amount of memory that will always be available to the shared pool.
+If more memory is needed at any moment, if it is available, it will be allocated to the shared pool over the 500M.
+Implement the above steps, monitor the database and let us know the result,
+</code>
+==== ORA-01103: database name ‘LOANE’ in control file is not ‘LOANM’ ====
+This can happen after a cloning or duplicate database operation and then trying to nid the new database.
+The db_name in the controlfile does not match the one in the init.ora file.
+=== Solution ===
+Change the DB_NAME parameter in spfile and restart the database so that the db_name's match.
+Then use nid to do the rename again.
+<code>
+startup nomount;
+alter system set db_name=<old db_name> scope=spfile;
+shutdown immediate;
+startup mount;
+alter database open resetlogs;
+</code>
+==== NID-00135: There are 1 active threads : oracle ====
+<code>
+SQL> select name,open_mode from v$database;
+NAME      OPEN_MODE
+--------- --------------------
+CLNE   MOUNTED
+$ nid target=/ dbname=clnm
+DBNEWID: Release 12.1.0.2.0 - Production on Fri Jul 27 06:01:10 2018
+Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
+Connected to database CLNE (DBID=34933270)
+NID-00135: There are 1 active threads
+Change of database name failed during validation - database is intact.
+DBNEWID - Completed with validation errors.
+</code>
+=== Cause ===
+There are active redo threads open. The database was probably not shutdown cleanly before running nid.
+=== Solution ===
+  *  Open the database cleanly
+<code>
+alter database open;
+Database altered.
+</code>
+  *  Shutdown the database
+<code>
+shut immediate;
+Database closed.
+Database dismounted.
+ORACLE instance shut down.
+</code>
+  *  Mount the database
+<code>
+startup mount
+ORACLE instance started.
+Total System Global Area 1.0737E+10 bytes
+Fixed Size 4582144 bytes
+Variable Size 5972690176 bytes
+Database Buffers 4731174912 bytes
+Redo Buffers 28971008 bytes
+Database mounted.
+</code>
+  *  Run nid again
+<code>
+nid target=sys/****** dbname=clnm
+DBNEWID: Release 12.1.0.2.0 - Production on Fri Jul 27 06:13:53 2018
+Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
+Connected to database clne (DBID=34933270)
+Connected to server version 12.1.0
+Control Files in database:
+/u01/oradata/clne/data/control01.ctl
+/u01/oradata/clne/idx/control02.ctl
+/u01/oradata/clne/redo1/control03.ctl
+Change database name of database clne to clnm? (Y/[N]) => y
+Proceeding with operation
+Changing database name from clne to clnm
+Control File /u01/oradata/clne/data/control01.ctl - modified
+Control File /u01/oradata/clne/idx/control02.ctl - modified
+Control File /u01/oradata/clne/redo1/control03.ctl - modified
+Datafile /u01/oradata/clne/data/system01.db - wrote new name
+Datafile /u01/oradata/clne/data/sysaux01.db - wrote new name
+.
+.
+.
+Datafile /u01/oradata/clne/undo/temp02.db - wrote new name
+Datafile /u01/oradata/clne/undo/temp03.db - wrote new name
+Datafile /u01/oradata/clne/idx/psgtt01.db - wrote new name
+Control File /u01/oradata/clne/data/control01.ctl - wrote new name
+Control File /u01/oradata/clne/idx/control02.ctl - wrote new name
+Control File /u01/oradata/clne/redo1/control03.ctl - wrote new name
+Instance shut down
+Database name changed to clnm.
+Modify parameter file and generate a new password file before restarting.
+Succesfully changed database name.
+DBNEWID - Completed succesfully.
+</code>
+=== Here is a script to do everything ===
+<code>
+#!/usr/bin/ksh
+# ==============================================================================
+# Name         : rename_database_after_ORA-01103.ksh
+# Description  : Renames a database after ORA-01103
+#
+# Parameters   : sid in the Controlfile
+#                sid in the init / spfile
+#
+# Example      : ./rename_database_after_ORA-01103.ksh loane loanm
+#
+# Notes        : Script to save having to remember which way around to change
+#                the names
+#
+# Modification History
+# ====================
+# When      Who               What
+# ========= ================= ==================================================
+# 02-MAY-20 Stuart Barkley    Created
+# ==============================================================================
+OLDSID=$1
+NEWSID=$2
+ORATAB="/etc/oratab"
+if [[ $# -ne 2 ]]; then
+    echo "Usage: $(basename $0): <sid in controlfile> <sid in spfile>"
+    echo "If you get a message like this:"
+    echo "ORA-01103: database name ‘LOANE’ in control file is not ‘LOANM’"
+    echo "then run this:
+    echo "./rename_database_after_ORA-01103.ksh loane loanm"
+    exit 1
+fi
+grep "^${OLDSID}:" "${ORATAB}" >/dev/null 2>&1
+[[ $? -ne 0 ]] && echo "${OLDSID} not in ${ORATAB}" && exit 1
+grep "^${NEWSID}:" "${ORATAB}" >/dev/null 2>&1
+[[ $? -ne 0 ]] && echo "${NEWSID} not in ${ORATAB}" && exit 1
+unset ORACLE_HOME
+ORAENV_ASK=NO
+export ORACLE_SID="${NEWSID}"
+. oraenv
+[[ "${ORACLE_HOME}" == "" ]] && echo "Cannot set environment for ${NEWSID}" && exit 1
+TIMESTAMP=`date +'%Y%m%d%H%M%S'`
+cd $ORACLE_HOME/dbs
+cp "init${NEWSID}.ora" "init${NEWSID}.ora.${TIMESTAMP}"
+perl -p -i -e 'if (m/db_name=/ or m/service_names=/) {s/'${NEWSID}'/'${OLDSID}'/i}' "init${NEWSID}.ora"
+perl -n -i -e 'print unless m/instance_name=/' "init${NEWSID}.ora"
+$ORACLE_HOME/bin/sqlplus / as sysdba<<EOSQL
+whenever sqlerror exit failure
+prompt startup force nomount pfile='init${NEWSID}.ora';
+startup force nomount pfile='init${NEWSID}.ora';
+prompt create spfile from pfile;
+create spfile from pfile;
+prompt alter database mount;
+alter database mount;
+prompt alter database open;
+alter database open;
+prompt shutdown immediate;
+shutdown immediate;
+prompt startup mount;
+startup mount;
+EOSQL
+[[ $? -ne 0 ]] && echo "Something went wrong" && exit 1
+nid target=/ dbname=${NEWSID} logfile="/tmp/nid_${OLDSID}_${NEWSID}.log"
+grep 'DBNEWID - Completed succesfully' "/tmp/nid_${OLDSID}_${NEWSID}.log" >/dev/null 2>&1
+[[ $? -ne 0 ]] && echo "Something went wrong with nid" && exit 1
+perl -p -i -e 'if (m/db_name=/ or m/service_names=/) {s/'${OLDSID}'/'${NEWSID}'/i}' "init${NEWSID}.ora"
+[[ $? -ne 0 ]] && echo "Something went wrong with renaming ${OLDSID} to ${NEWSID}" && exit 1
+$ORACLE_HOME/bin/sqlplus / as sysdba<<EOSQL
+prompt create spfile from pfile;
+create spfile from pfile;
+prompt startup mount;
+startup mount;
+prompt alter database open resetlogs;
+alter database open resetlogs;
+EOSQL
+[[ $? -ne 0 ]] && echo "Something went wrong with renaming ${OLDSID} to ${NEWSID}" && exit 1
+echo "Success"
+</code>
+==== ORA-00392: log 1 of thread 1 is being cleared, operation not allowed ====
 After performing an active duplication, this happened when trying to open the database.
-<code>107@@</code>
+<code>
-<code>108@@</code>
+SQL> alter database open resetlogs
+alter database open resetlogs
+  *
+ERROR at line 1:
+ORA-00392: log 1 of thread 1 is being cleared, operation not allowed
+ORA-00312: online log 1 thread 1: '/oracle/oradata/testdb/redo/redo01.log'
+</code>
+<code>
+SQL> select group#,thread#,status from v$log;
+GROUP#   THREAD#   STATUS
+-------  --------- ----------------
+        1         CLEARING_CURRENT
+        1         STALE
+        1         STALE
+</code>
 Check the alert log! Always a good idea to get extra info. In here we find the answer
-<code>109@@</code>
+<code>
-So the clue is that the file cannot be created at the operating system level.<br />
+ORA-00344: unable to re-create online log
-Doing a <code>df -k /oracle/oradata/testdb/redo</code> shows us that the filesystem was created too small for the destination database.<br />
+'/oracle/oradata/testdb/redo/redo01.log'
-In this case, we do not want the redolog directory to be as big as on the source db as there will be almost no movement on the destination.<br />
+ORA-27040: file create error, unable to create file
-I should have specified the LOGFILE parameter in the DUPLICATE clause to setup new, smaller redolog files.<br />
+Linux-x86_64 Error: 2: No such file or directory
-As this duplicate takes over 12 hours, I didn't want to do it again so I created symbolic links in the redolog directory pointing out to a larger filesystem with more space.<br />
+Additional information: 1
+</code>
+So the clue is that the file cannot be created at the operating system level.\\
+Doing a <code>df -k /oracle/oradata/testdb/redo</code> shows us that the filesystem was created too small for the destination database.\\
+In this case, we do not want the redolog directory to be as big as on the source db as there will be almost no movement on the destination.\\
+I should have specified the LOGFILE parameter in the DUPLICATE clause to setup new, smaller redolog files.\\
+As this duplicate takes over 12 hours, I didn't want to do it again so I created symbolic links in the redolog directory pointing out to a larger filesystem with more space.\\
 This allowed the creation of the redolog files. They can then be resized once the database has been opened.
-<code>110@@</code>
+<code>
+cd /oracle/oradata/testdb/redo
+ln -s /tmp/redo01.log redo01.log
+ln -s /tmp/redo02.log redo02.log
+ln -s /tmp/redo03.log redo03.log
-=====RMAN-04004: error from recovery catalog database: ORA-01017: invalid username/password; logon denied=====
+SQL> alter database open resetlogs
-<code>111@@</code>
+Database opened.
-This is normally very straight-forward and self-explanatory.<br />
+</code>
+==== RMAN-06136: Oracle error from auxiliary database: ORA-00344: unable to re-create online log '/cln/exp/ora_data1/dwh/redolog/redo01.log' ====
+<code>
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-03002: failure of Duplicate Db command at 11/26/2023 00:11:48
+RMAN-05501: aborting duplication of target database
+RMAN-03015: error occurred in stored script Memory Script
+RMAN-06136: Oracle error from auxiliary database: ORA-00344: unable to re-create online log '/cln/exp/ora_data1/dwh/redolog/redo01.log'
+ORA-27040: file create error, unable to create file
+IBM AIX RISC System/6000 Error: 2: No such file or directory
+Additional information: 1
+</code>
+After an RMAN duplicate, the open resetlogs fails with above message. Note the path shown is that of the source database.\\
+This means that the db_logfile_convert parameter was setup incorrectly. To fix the issue, we rebuild the logfile groups while the database is still in mount mode.
+<code>
+set lines 200 pages 200
+col member for a70
+select vl.group#
+,      bytes/1024/1024 mb
+,      type
+,      member
+from   v$log     vl
+,      v$logfile vlf
+where  vl.group# = vlf.group#
+order  by 1
+/
+</code>
+<code>
+    GROUP#         MB TYPE    MEMBER
+---------- ---------- ------- ----------------------------------------------------------------------
+       1000 ONLINE  /cln/exp/ora_data1/dwh/redolog/redo01.log
+       1000 ONLINE  /cln/exp/ora_data1/dwh/redolog/redo02.log
+       1000 ONLINE  /cln/exp/ora_data1/dwh/redolog/redo03.log
+       1000 ONLINE  /cln/exp/ora_data1/dwh/redolog/redo04.log
+       1000 ONLINE  /cln/exp/ora_data1/dwh/redolog/redo05.log
+       1000 ONLINE  /cln/exp/ora_data1/dwh/redolog/redo06.log
+       1000 ONLINE  /cln/exp/ora_data1/dwh/redolog/redo07.log
+       1000 ONLINE  /cln/exp/ora_data1/dwh/redolog/redo08.log
+       1000 ONLINE  /cln/exp/ora_data1/dwh/redolog/redo09.log
+       1000 ONLINE  /cln/exp/ora_data1/dwh/redolog/redo10.log
+rows selected.
+</code>
+The idea is to drop all these groups and re-create new ones with members is the correct (better) place.
+<code>
+alter database drop logfile group 2;
+alter database drop logfile group 3;
+alter database drop logfile group 4;
+alter database drop logfile group 5;
+alter database drop logfile group 6;
+alter database drop logfile group 7;
+alter database drop logfile group 8;
+alter database drop logfile group 9;
+alter database drop logfile group 10;
+</code>
+<code>
+Database altered.
+SQL>
+Database altered.
+SQL>
+Database altered.
+SQL>
+Database altered.
+SQL>
+Database altered.
+SQL>
+Database altered.
+SQL>
+Database altered.
+SQL>
+Database altered.
+SQL> alter database drop logfile group 9
+*
+ERROR at line 1:
+ORA-01567: dropping log 9 would leave less than 2 log files for instance dwhaa2
+(thread 1)
+ORA-00312: online log 9 thread 1:
+'/cln/exp/ora_data1/dwh/redolog/redo09.log'
+</code>
+Ok, so we cannot remve the last 2 just yet. Let's recreate the first 8
+<code>
+alter database add logfile group 2 ('/cln/acc/ora_data/dwhaa2/redo02.log' ) size 1G reuse;
+alter database add logfile group 3 ('/cln/acc/ora_data/dwhaa2/redo03.log' ) size 1G reuse;
+alter database add logfile group 4 ('/cln/acc/ora_data/dwhaa2/redo04.log' ) size 1G reuse;
+alter database add logfile group 5 ('/cln/acc/ora_data/dwhaa2/redo05.log' ) size 1G reuse;
+alter database add logfile group 6 ('/cln/acc/ora_data/dwhaa2/redo06.log' ) size 1G reuse;
+alter database add logfile group 7 ('/cln/acc/ora_data/dwhaa2/redo07.log' ) size 1G reuse;
+alter database add logfile group 8 ('/cln/acc/ora_data/dwhaa2/redo08.log' ) size 1G reuse;
+</code>
+<code>
+Database altered.
+SQL>
+Database altered.
+SQL>
+Database altered.
+SQL>
+Database altered.
+SQL>
+Database altered.
+SQL>
+Database altered.
+SQL>
+Database altered.
+SQL>
+Database altered.
+SQL>
+</code>
+<code>
+SQL> select group#,thread#,status from v$log;
+    GROUP#    THREAD# STATUS
+---------- ---------- ----------------
+          1 UNUSED
+          1 UNUSED
+          1 UNUSED
+          1 UNUSED
+          1 UNUSED
+          1 UNUSED
+          1 UNUSED
+          1 UNUSED
+          1 CLEARING
+          1 CLEARING_CURRENT
+rows selected.
+</code>
+As we have now created more groups, we should be able to drop the remaining 2 groups
+<code>
+SQL> alter database drop logfile group 9;
+alter database drop logfile group 10;
+Database altered.
+SQL> alter database drop logfile group 10
+*
+ERROR at line 1:
+ORA-01623: log 10 is current log for instance dwhaa2 (thread 1) - cannot drop
+ORA-00312: online log 10 thread 1:
+'/cln/exp/ora_data1/dwh/redolog/redo10.log'
+</code>
+One gone, but the last one won't go because it is current. And we can't switch logfiles because the database is not open!\\
+What now?\\
+The only way is to rename the logfile in the controlfile to an existing file. So let's duplicate on of the new members at o/s level and assign it.
+<code>
+SQL> !cp -p /cln/acc/ora_data/dwhaa2/redo07.log /cln/acc/ora_data/dwhaa2/redo10.log
+SQL> alter database rename file '/cln/exp/ora_data1/dwh/redolog/redo10.log' to '/cln/acc/ora_data/dwhaa2/redo10.log';
+Database altered.
+</code>
+Ok, good sign! Let's check
+<code>
+SQL> select group#,thread#,status from v$log;
+    GROUP#    THREAD# STATUS
+---------- ---------- ----------------
+          1 UNUSED
+          1 UNUSED
+          1 UNUSED
+          1 UNUSED
+          1 UNUSED
+          1 UNUSED
+          1 UNUSED
+          1 UNUSED
+          1 CLEARING_CURRENT
+rows selected.
+SQL> alter database clear log group 10;
+alter database clear log group 10
+                     *
+ERROR at line 1:
+ORA-01900: LOGFILE keyword expected
+SQL> alter database clear logfile group 10;
+Database altered.
+SQL>  select group#,thread#,status from v$log;
+    GROUP#    THREAD# STATUS
+---------- ---------- ----------------
+          1 UNUSED
+          1 UNUSED
+          1 UNUSED
+          1 UNUSED
+          1 UNUSED
+          1 UNUSED
+          1 UNUSED
+          1 UNUSED
+          1 CURRENT
+rows selected.
+<code>
+And finally, let's see if it opens
+<code>
+SQL> alter database open resetlogs;
+Database altered.
+SQL>
+</code>
+To tidy up, we could now re-create the dropped group 9.
+==== ORA-01017: invalid username/password; logon denied when connecting using slash (sqlplus / as sysdba) ====
+Trying to connect using "/ as sysdba", the ORA-01017 appears:
+<code>
+(0) upg oracle@hn5114:/home/oracle> sqlplus / as sysdba
+SQL*Plus: Release 18.0.0.0.0 - Production on Fri Sep 20 10:16:22 2019
+Version 18.4.0.0.0
+Copyright (c) 1982, 2018, Oracle.  All rights reserved.
+ERROR:
+ORA-01017: invalid username/password; logon denied
+</code>
+but trying to connect using "sys as sysdba", the connection is successfull
+<code>
+(0) upg oracle@hn5114:/home/oracle> sqlplus sys as sysdba
+SQL*Plus: Release 18.0.0.0.0 - Production on Fri Sep 20 10:16:07 2019
+Version 18.4.0.0.0
+Copyright (c) 1982, 2018, Oracle.  All rights reserved.
+Enter password:
+Connected to an idle instance.
+SQL>
+</code>
+Things to check:
+  * sqlplus / as sysdba means you use o/s authorisation so the o/s user must be a member of dba group otherwise the connection will fail.
+  * sqlplus sys/pass@sid as sysdba means you use passwordfile authorisation. In this case you need to properly configure the orapw file and set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE or SHARED.
+  * Check the sqlnet.ora file. Operating system authentication will be disabled if you have SQLNET.AUTHENTICATION_SERVICES=NONE in there.
+==== RMAN-04004: error from recovery catalog database: ORA-01017: invalid username/password; logon denied ====
+<code>
+oracle@host:/export/home/ora> rman target / catalog catowner/catpass@catdb
+Recovery Manager: Release 11.2.0.2.0 - Production on Thu Jun 12 16:51:03 2014
+Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
+connected to target database: XXXXRAP1 (DBID=3557010742)
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-00554: initialization of internal recovery manager package failed
+RMAN-04004: error from recovery catalog database: ORA-01017: invalid username/password; logon denied
+</code>
+This is normally very straight-forward and self-explanatory.\\
 The puzzle here is that a connection from SQL*Plus works!
-<code>112@@</code>
+<code>
+sqlplus catowner/catpass@catdb
+SQL*Plus: Release 11.2.0.2.0 Production on Thu Jun 12 16:53:18 2014
+Copyright (c) 1982, 2010, Oracle.  All rights reserved.
+Connected to:
+Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
+With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
+SQL>
+</code>
 Eventually worked out that the rman binary in the databases ORACLE_HOME on the source machine needed relinking!
-<code>113@@</code>
+<code>
+cd $ORACLE_HOME/rdbms/lib
+make -f ins_rdbms.mk irman
+</code>
-=====ORA-00230: operation disallowed: snapshot control file enqueue unavailable=====
+==== ORA-00230: operation disallowed: snapshot control file enqueue unavailable ====
-<code>114@@</code>
+<code>
+waiting for snapshot control file enqueue
+waiting for snapshot control file enqueue
+waiting for snapshot control file enqueue
+waiting for snapshot control file enqueue
+cannot make a snapshot control file
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-03008: error while performing automatic resync of recovery catalog
+ORA-00230: operation disallowed: snapshot control file enqueue unavailable
+</code>
 From the doc...
-<code>115@@</code>
+<code>
+When RMAN needs to back up or resynchronize from the control file, it first creates a snapshot or consistent image of the control file.
+If one RMAN job is already backing up the control file while another needs to create a new snapshot control file, then you may see the following message:
+waiting for snapshot controlfile enqueue
+Under normal circumstances, a job that must wait for the control file enqueue waits for a brief interval and then successfully obtains the enqueue.
+RMAN makes up to five attempts to get the enqueue and then fails the job.
+The conflict is usually caused when two jobs are both backing up the control file, and the job that first starts backing up the control file waits for service from the media manager.
+To determine which job is holding the conflicting enqueue:
+    After you see the first message stating "RMAN-08512: waiting for snapshot controlfile enqueue", start a new SQL*Plus session on the target database:
+    % sqlplus 'SYS/oracle@trgt AS SYSDBA'
+    Execute the following query to determine which job is causing the wait:
+    SELECT s.SID, USERNAME AS "User", PROGRAM, MODULE,
+           ACTION, LOGON_TIME "Logon", l.*
+    FROM V$SESSION s, V$ENQUEUE_LOCK l
+    WHERE l.SID = s.SID
+    AND l.TYPE = 'CF'
+    AND l.ID1 = 0
+    AND l.ID2 = 2;
+    You should see output similar to the following (the output in this example has been truncated):
+    SID User Program              Module                    Action           Logon
+    --- ---- -------------------- ------------------------- ---------------- ---------
+SYS  rman@h13 (TNS V1-V3) backup full datafile: c1  0000210 STARTED  21-JUN-01
+After you have determined which job is creating the enqueue, you can do one of the following:
+    Wait until the job creating the enqueue completes
+    Cancel the current job and restart it after the job creating the enqueue completes
+    Cancel the job creating the enqueue
+Commonly, enqueue situations occur when a job is writing to a tape drive, but the tape drive is waiting for a new cassette to be inserted.
+If you start a new job in this situation, then you will probably receive the enqueue message because the first job cannot complete until the new tape is loaded.
+</code>
 or more directly...
-<code>116@@</code>
+<code>
+set lines 2000
+col killer     for a70
+col program    for a20
+col module     for a20
+col action     for a20
+col logon_time for a20
+select 'alter system disconnect session **||s.sid||','||s.serial#||** immediate;' killer
+,      username
+,      program
+,      module
+,      action
+,      logon_time
+from   v$session s
+,      v$enqueue_lock l
+where  l.sid  = s.sid
+and    l.type = 'cf'
+and    l.id1  = 0
+and    l.id2  = 2
+/
+</code>
-=====ORA-19511: non RMAN, but media manager or vendor specific failure, error text:=====
+==== ORA-19511: non RMAN, but media manager or vendor specific failure, error text: ====
-<code>117@@</code>
+<code>
-If this happens, check to see if the nsr daemon is running...<br />
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-03002: failure of Duplicate Db command at 05/01/2018 14:23:09
+RMAN-05501: aborting duplication of target database
+RMAN-03015: error occurred in stored script Memory Script
+ORA-27191: sbtinfo2 returned error
+Additional information: 3586
+ORA-19511: non RMAN, but media manager or vendor specific failure, error text:
+   Unable to connect to NetWorker server 'hn6000.cln.be' because 'Authentication error; why = Invalid client credential'. (2:10:79)
+Recovery Manager complete.
+</code>
+If this happens, check to see if the nsr daemon is running...\\
 Good system:
-<code>118@@</code>
+<code>
+/home/oracle> ps -ef | grep nsr
+    root  5701836        1   0   Mar 16      - 13:18 /bin/nsrexecd
+  oracle 18809292 22741502   0 15:03:58  pts/0  0:00 grep nsr
+</code>
 Bad system:
-<code>119@@</code>
+<code>
+ /home/oracle> ps -ef | grep nsr
+  oracle 14745986 15139292   0 15:03:29  pts/2  0:00 grep nsr
+</code>
-=====ORA-19554: error allocating device, device type: SBT_TAPE, device name:=====
+==== ORA-19554: error allocating device, device type: SBT_TAPE, device name: ====
 If using Cygwin...
-<code>120@@</code>
+<code>
-  * Check the tdpo.opt file is where you think it is!
+RMAN-00571: ===========================================================
-  * Check the path to the opt file looks like this (needs to be Windows format):
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
-<code>121@@</code>
+RMAN-00571: ===========================================================
-  * Check the content of the opt file is Windows format paths!
+RMAN-03002: failure of backup command at 05/19/2015 12:25:47
+ORA-19554: error allocating device, device type: SBT_TAPE, device name:
+ORA-27000: skgfqsbi: failed to initialize storage subsystem (SBT) layer
+OSD-02534: Message 2534 not found;  product=RDBMS; facility=SOSD
+</code>
+  *  Check the tdpo.opt file is where you think it is!
+  *  Check the path to the opt file looks like this (needs to be Windows format):
+<code>
+CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' FORMAT   'LOG_%d_t%t_s%s_u%U' PARMS  'ENV=(TDPO_OPTFILE=E:\\oracle\\KITRYD\\admin\\tdpo.opt)';
+</code>
+  *  Check the content of the opt file is Windows format paths!
-=====RMAN-20033: control file SEQUENCE# too low=====
+==== RMAN-20033: control file SEQUENCE# too low ====
-<code>122@@</code>
+<code>
-From [[https://forums.oracle.com/forums/thread.jspa?threadID=1115969|forums.oracle.com]]<br />
+RMAN-00571: ===========================================================
-This error generally happens if an archivelog backup happens while the RMAN database backup is running and if controlfile autobackup is configured.<br />
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-03008: error while performing automatic resync of recovery catalog
+==== RMAN-20033: control file SEQUENCE# too low ====
+</code>
+From [[https://forums.oracle.com/forums/thread.jspa?threadID=1115969|forums.oracle.com]]\\
+This error generally happens if an archivelog backup happens while the RMAN database backup is running and if controlfile autobackup is configured.\\
 The database backup is usually successful and it is just the controlfile backup that fails.
-=====ORA-27302: failure occurred at: slgpn=====
+==== ORA-27302: failure occurred at: slgpn ====
-<code>123@@</code>
+<code>
-====Problem====
+RMAN-03009: failure of Control File and SPFILE Autobackup command on c1 channel at 06/28/2011 18:59:38
+ORA-19715: invalid format %b for generated name
+ORA-27302: failure occurred at: slgpn
+</code>
+=== Problem ===
 When doing a "<tt>backup as copy database</tt>", got these errors after 145 of 147 datafiles had been backed up!
-====Investigation====
+=== Investigation ===
-I had read [[https://oracleedge.wordpress.com/2011/06/29/oracle-rman-03009-ora-19715-ora-27302/|elsewhere]] that this could be something to do with the format of the autobackup of the control file so I changed the format as suggested and reran the backup.<br />
+I had read [[https://oracleedge.wordpress.com/2011/06/29/oracle-rman-03009-ora-19715-ora-27302/|elsewhere]] that this could be something to do with the format of the autobackup of the control file so I changed the format as suggested and reran the backup.\\
-No luck. It ended exactly the same way after 145 files. I decided to compare the original list of datafiles with those that had been backed up in order to track down the 2 failures. Maybe I could see something odd with them.<br />
+No luck. It ended exactly the same way after 145 files. I decided to compare the original list of datafiles with those that had been backed up in order to track down the 2 failures. Maybe I could see something odd with them.\\
 Sure enough, these 2 files were different... there was a space at the **end** of the filenames :-)
-<code>124@@</code>
+<code>
+select 'x'||file_name||'x'
+from   dba_data_files
+where  file_name like '% %'
+/
+</code>
-====Solution====
+=== Solution ===
 Run the backup of the other 2 datafiles with a different format that did not rely on the filename.
-<code>125@@</code>
+<code>
-The other 145 datafiles were backed up with a format of '/path/to/datafiles/%b' which preserves the filenames so all that needs to be done is rename the other 2 after the backup finishes.<br />
+run {
+    allocate channel c1 device type disk format '/path/to/datafiles/%U';
+    backup as copy datafile 170;
+    backup as copy datafile 171;
+    backup as copy current controlfile;
+    release channel c1;
+}
+</code>
+The other 145 datafiles were backed up with a format of '/path/to/datafiles/%b' which preserves the filenames so all that needs to be done is rename the other 2 after the backup finishes.\\
 If they are going to be used in a restore scenario, they should be renamed in the controlfile also.
-<code>126@@</code>
+<code>
+alter database rename file '/path/to/datafiles/filename_with_a_space_at_end ' to '/path/to/datafiles/filename_without_a_space';
+</code>
-=====RMAN errors after crosscheck backup and delete noprompt obsolete and delete noprompt expired backup=====
+==== RMAN errors after crosscheck backup and delete noprompt obsolete and delete noprompt expired backup ====
-<code>127@@</code>
+<code>
-Use the FORCE option with the DELETE command<br />
+RMAN-06207: WARNING: 3921 objects could not be deleted for SBT_TAPE channel(s) due
+RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
+RMAN-06210: List of Mismatched objects
+RMAN-06211: ==========================
+RMAN-06212:   Object Type   Filename/Handle
+RMAN-06213: --------------- ---------------------------------------------------
+RMAN-06214: Backup Piece    DB_SID_t20140316_s263889_umhp3bpa7_1_1
+...
+</code>
+Use the FORCE option with the DELETE command\\
 The FORCE command tells RMAN to clean the information out of the catalog regardless of whether it can find it on the media or not.
-<code>128@@</code>
+<code>
-=====RMAN-06004: ORACLE error from recovery catalog database: RMAN-20003: target database incarnation not found in recovery catalog=====
+delete force noprompt expired backup;
-====Problem:====
+delete force noprompt obsolete;
-<code>129@@</code>
+</code>
-====Solution:====
+==== RMAN-00600: internal error, arguments [8714] [] [] [] [] ====
-Strangely, this means, in my particular case, that block change tracking is turned on in the target databse and needs turning off in the auxiliary while the DUPLICATE is running.<br />
+Internal error displayed when trying to restore a database
-It must be done while the auxiliary is in mount mode and restoring the datafiles (before recovery starts)<br />
+<code>
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-03002: failure of restore command at 01/04/2022 16:16:24
+RMAN-00600: internal error, arguments [8714] [] [] [] []
+</code>
+As Oracle mentions [[https://docs.oracle.com/en/database/oracle/oracle-database/19/rcmrf/RESET-DATABASE.html#GUID-C0C00F35-F21F-4657-AE9B-652C4DCE5817||here]], it is normal bahaviour!
+Due to the fact that an alter database open resetlogs seems to be have done without being connected to the catalog, the said catalog is now confused as to which incarnation to use as current. The solution is either:
+  * Restore the database without a catalog connection
+  * Tell the catalog which incarnation to use
+<code>
+RMAN> list incarnation of database;
+List of Database Incarnations
+DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
+------- ------- -------- ---------------- --- ---------- ----------
+551048814 551050752 EBST     3456740606       PARENT  107183355928 07-OCT-12
+551048814 551048815 EBST     3456740606       PARENT  331802566280 31-DEC-19
+551048814 741634576 EBST     3456740606       CURRENT 390104839590 29-DEC-21
+RMAN> reset database to incarnation 551048815;
+database reset to incarnation 551048815
+RMAN> list incarnation of database;
+List of Database Incarnations
+DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
+------- ------- -------- ---------------- --- ---------- ----------
+551048814 551050752 EBST     3456740606       PARENT  107183355928 07-OCT-12
+551048814 551048815 EBST     3456740606       CURRENT 331802566280 31-DEC-19
+551048814 741634576 EBST     3456740606       ORPHAN  390104839590 29-DEC-21
+RMAN>
+</code>
+==== RMAN-06004: ORACLE error from recovery catalog database: RMAN-20242: specification does not match any archived log in the repository ====
+After restoring archivelogs from log sequence, this error is shown.
+<code>
+RMAN> run {
+> allocate channel t0 type sbt_tape;
+> allocate channel t1 type sbt_tape;
+> allocate channel t2 type sbt_tape;
+> restore archivelog from logseq 40410 until logseq 45410;
+> }
+released channel: ORA_DISK_1
+allocated channel: t0
+channel t0: SID=406 device type=SBT_TAPE
+channel t0: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
+allocated channel: t1
+channel t1: SID=421 device type=SBT_TAPE
+channel t1: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
+allocated channel: t2
+channel t2: SID=436 device type=SBT_TAPE
+channel t2: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
+Starting restore at 2022-01-04 21:33:17
+released channel: t0
+released channel: t1
+released channel: t2
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-03002: failure of restore command at 01/04/2022 21:33:17
+RMAN-06004: ORACLE error from recovery catalog database: RMAN-20242: specification does not match any archived log in the repository
+</code>
+Run a crosscheck of the archivelogs
+<code>
+RMAN> run {
+allocate channel t0 type sbt_tape;
+allocate channel t1 type sbt_tape;
+allocate channel t2 type sbt_tape;
+crosscheck archivelog all;
+}
+> 3> 4> 5> 6>
+allocated channel: t0
+channel t0: SID=406 device type=SBT_TAPE
+channel t0: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
+allocated channel: t1
+channel t1: SID=421 device type=SBT_TAPE
+channel t1: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
+allocated channel: t2
+channel t2: SID=436 device type=SBT_TAPE
+channel t2: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
+validation succeeded for archived log
+archived log file name=/cln/tst/ora_ebst/db/apps_st/data/archive/1_40364_1028505496.dbf RECID=40396 STAMP=1093123225
+validation succeeded for archived log
+archived log file name=/cln/tst/ora_ebst/db/apps_st/data/archive/1_40365_1028505496.dbf RECID=40399 STAMP=1093123225
+validation succeeded for archived log
+archived log file name=/cln/tst/ora_ebst/db/apps_st/data/archive/1_40366_1028505496.dbf RECID=40413 STAMP=1093123225
+validation succeeded for archived log
+archived log file name=/cln/tst/ora_ebst/db/apps_st/data/archive/1_40367_1028505496.dbf RECID=40405 STAMP=1093123225
+validation succeeded for archived log
+archived log file name=/cln/tst/ora_ebst/db/apps_st/data/archive/1_40368_1028505496.dbf RECID=40409 STAMP=1093123225
+validation succeeded for archived log
+archived log file name=/cln/tst/ora_ebst/db/apps_st/data/archive/1_40369_1028505496.dbf RECID=40398 STAMP=1093123225
+validation succeeded for archived log
+archived log file name=/cln/tst/ora_ebst/db/apps_st/data/archive/1_40370_1028505496.dbf RECID=40401 STAMP=1093123225
+...
+archived log file name=/cln/tst/ora_ebst/db/apps_st/data/archive/1_40409_1028505496.dbf RECID=40440 STAMP=1093123251
+Crosschecked 46 objects
+released channel: t0
+released channel: t1
+released channel: t2
+RMAN> exit
+Recovery Manager complete.
+</code>
+Then attempt to open the database
+<code>
+sqlplus / as sysdba
+SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 4 21:35:31 2022
+Copyright (c) 1982, 2014, Oracle.  All rights reserved.
+Connected to:
+Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
+With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
+SQL> alter database open resetlogs;
+Database altered.
+SQL>
+</code>
+==== RMAN-06004: ORACLE error from recovery catalog database: RMAN-20003: target database incarnation not found in recovery catalog ====
+=== Problem: ===
+<code>
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-03002: failure of Duplicate Db command at 12/05/2017 11:11:15
+RMAN-05501: aborting duplication of target database
+RMAN-03015: error occurred in stored script Memory Script
+RMAN-06004: ORACLE error from recovery catalog database: RMAN-20003: target database incarnation not found in recovery catalog
+</code>
+=== Solution: ===
+Strangely, this means, in my particular case, that block change tracking is turned on in the target database and needs turning off in the auxiliary while the DUPLICATE is running.\\
+It must be done while the auxiliary is in mount mode and restoring the datafiles (before recovery starts)\\
 It is reported to be a bug fixed in 11.1 but somehow, we're still (sometimes) getting it in 12.1.0.2.
-=====RMAN-06059: expected archived log not found, loss of archived log compromises recoverability=====
+==== RMAN-06059: expected archived log not found, loss of archived log compromises recoverability ====
-<code>130@@</code>
+<code>
-This error can come from the fact that the database files (more likely archivelogs) are being deleted by the operating system so RMAN has no clue of their whereabouts.<br />
+RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
-Possibly result of a database duplication and the catalog has not updated properly?<br />
+ORA-19625: error identifying file <filename>
-====Solution====
+</code>
-<code>131@@</code>
+This error can come from the fact that the database files (more likely archivelogs) are being deleted by the operating system so RMAN has no clue of their whereabouts.\\
+Possibly result of a database duplication and the catalog has not updated properly?\\
+=== Solution ===
+<code>
+crosscheck archivelog all;
+</code>
 And then maybe take a backup to be on the safe side.
-=====RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause=====
+==== RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause ====
-====Problem:====
+=== Problem: ===
-<code>132@@</code>
+<code>
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-03002: failure of Duplicate Db command at 12/05/2017 14:17:49
+RMAN-05501: aborting duplication of target database
+RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause
+</code>
 Usually happens after restarting a failed RMAN DUPLICATE database. The spfile has been regenerated on the auxiliary to a level where it has too much detail in it.
-====Solution:====
+=== Solution: ===
-<code>133@@</code>
+<code>
+$ cd $ORACLE_HOME/dbs
+$ ls -altr *dwh_perf*
+-rw-r-----    1 oracle   oinstall   17645568 Aug  3 13:34 snapcf_dwh_perf.f
+-rw-r-----    1 oracle   oinstall       7680 Nov  4 09:22 orapwdwh_perf
+-rw-r--r--    1 oracle   oinstall        104 Dec  5 09:34 initdwh_perf.ora
+-rw-r-----    1 oracle   oinstall       6656 Dec  5 11:11 spfiledwh_perf.ora
+-rw-rw----    1 oracle   oinstall       1544 Dec  5 14:18 hc_dwh_perf.dat
+$ mv hc_dwh_perf.dat hc_dwh_perf.dat.old
+$ mv spfiledwh_perf.ora spfiledwh_perf.ora.old
+$ cat initdwh_perf.ora
+sga_max_size='6G'
+sga_target='6G'
+compatible='12.0.0'
+db_files='500'
+  -  Instance name
+db_name=dwh_perf
+</code>
 Re-run the DUPLICATE. It should zoom past the already recovered datafiles.
-=====PSDRPC returns significant error 1013=====
+==== RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/cln/acc/ora_ebso/db/apps_st/data/archive/1_138526_796044402.dbf' ====
-<code>134@@</code>
+<code>
-====Action====
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-03002: failure of Duplicate Db command at 11/07/2019 18:01:02
+RMAN-05501: aborting duplication of target database
+RMAN-03015: error occurred in stored script Memory Script
+RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/cln/acc/ora_ebso/db/apps_st/data/archive/1_138526_796044402.dbf'
+ORA-00308: cannot open archived log '/cln/acc/ora_ebso/db/apps_st/data/archive/1_138526_796044402.dbf'
+ORA-27037: unable to obtain file status
+IBM AIX RISC System/6000 Error: 2: No such file or directory
+Additional information: 3
+Recovery Manager complete.
+</code>
+or
+<code>
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-03002: failure of Duplicate Db command at 12/06/2019 20:56:59
+RMAN-05501: aborting duplication of target database
+RMAN-03015: error occurred in stored script Memory Script
+RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/cln/kap/ora_data1/common/archivelog/upgbench/log1_263667_815790039.arc'
+ORA-10877: error signaled in parallel recovery slave
+</code>
+No real clues here but it is the block change tracking file that is active on the source and must be disabled on the destination before recovery starts. So as soon as the controlfile has been restored and the database mounted, login to another session, connect to the database and issue
+<code>
+SQL> alter database disable block change tracking;
+Database altered.
+SQL> exit
+</code>
+The DUPLICATE should then continue to recover correctly.
+==== PSDRPC returns significant error 1013 ====
+<code>
+archived log thread=1 sequence=195008
+channel t2: reading from backup piece arclog_LBK_4260753020_946042770_479194_1
+channel t0: piece handle=arclog_LBK_4260753020_946042770_479192_1 tag=TAG20170607T133757
+channel t0: restored backup piece 1
+channel t0: restore complete, elapsed time: 00:00:35
+archived log file name=/xxxxxx/acc/ora_bin2/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch1_195006_815790039.dbf thread=1 sequence=195006
+PSDRPC returns significant error 1013.
+PSDRPC returns significant error 1013.
+released channel: t0
+released channel: t1
+released channel: t2
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-03002: failure of Duplicate Db command at 06/09/2017 15:15:04
+RMAN-05501: aborting duplication of target database
+RMAN-03015: error occurred in stored script Memory Script
+RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/xxxxxx/acc/ora_bin2/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch1_195006_815790039.dbf'
+ORA-10877: error signaled in parallel recovery slave
+Recovery Manager complete.
+</code>
+=== Action ===
 Check the alert log on the auxiliary instance.
-<code>135@@</code>
+<code>
+ORA-279 signalled during: alter database recover logfile '/xxxxxx/acc/ora_bin2/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch1_195005_815790039.dbf'...
+alter database recover logfile '/xxxxxx/acc/ora_bin2/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch1_195006_815790039.dbf'
+Media Recovery Log /xxxxxx/acc/ora_bin2/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch1_195006_815790039.dbf
+Fri Jun 09 07:30:30 2017
+Errors with log /xxxxxx/acc/ora_bin2/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch1_195006_815790039.dbf
+Recovery interrupted!
+Recovered data files to a consistent state at change 238029030495
+Media Recovery failed with error 19755
+Errors in file /xxxxxx/acc/ora_bin2/app/oracle/diag/rdbms/lbkro/lbkro/trace/lbkro_pr00_13108374.trc:
+ORA-00283: recovery session canceled due to errors
+==== >ORA-19755: could not open change tracking file
+==== >ORA-19750: change tracking file: '/xxxxxx/exp/ora_data2/lbk/data/bct_lbk_do_not_delete.f
+==== >ORA-27037: unable to obtain file status
+==== >IBM AIX RISC System/6000 Error: 2: No such file or directory
+Additional information: 3
+Fri Jun 09 07:30:36 2017
+ORA-10877 signalled during: alter database recover logfile '/xxxxxx/acc/ora_bin2/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch1_195006_815790039.dbf'...
+Fri Jun 09 14:04:57 2017
+alter database recover logfile '/xxxxxx/acc/ora_bin2/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch1_195006_815790039.dbf'
+Media Recovery Log /xxxxxx/acc/ora_bin2/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch1_195006_815790039.dbf
+Media Recovery failed with error 1112
+ORA-283 signalled during: alter database recover logfile '/xxxxxx/acc/ora_bin2/app/oracle/product/11.2.0.4/dbhome_1/dbs/arch1_195006_815790039.dbf'...
+Fri Jun 09 14:31:20 2017
+Shutting down instance (immediate)
+Shutting down instance: further logons disabled
+Stopping background process MMNL
+Stopping background process MMON
+License high water mark = 49
+ALTER DATABASE CLOSE NORMAL
+ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
+ALTER DATABASE DISMOUNT
+Shutting down archive processes
+Archiving is disabled
+Completed: ALTER DATABASE DISMOUNT
+ARCH: Archival disabled due to shutdown: 1089
+Shutting down archive processes
+Archiving is disabled
+ARCH: Archival disabled due to shutdown: 1089
+Shutting down archive processes
+Archiving is disabled
+Fri Jun 09 14:31:24 2017
+</code>
 This is a known bug. it is fixed in 11.2.0.3 but... this customer does not apply patches!
-====Solution====
+=== Solution ===
 While the DUPLICATE is running (and in MOUNT mode), log on to the auxiliary instance and disable block change tracking.
-<code>136@@</code>
+<code>
-A documented workaround is to set db_filename_convert parameter in the DUPLICATE clause of the run block but I tried this and it failed again.<br />
+alter database disable block change tracking;
+</code>
+A documented workaround is to set db_filename_convert parameter in the DUPLICATE clause of the run block but I tried this and it failed again.\\
 The obvious solution is to patch the database but...
-=====Database trigger to capture ORA errors=====
+==== Database trigger to capture ORA errors ====
 From [[http://ora-ssn.blogspot.be/2011/10/trigger-on-database-to-capture-ora.html|ora-ssn.blogspot.be]]
-<code>137@@</code>
+<code>
+CREATE TABLE stats$error_log (
+        err_dt          TIMESTAMP,
+        db_user         VARCHAR2(30),
+        msg_stack       VARCHAR2(2000),
+        sqltxt          VARCHAR2(1000))
+tablespace users;
+</code>
 Now, create a trigger on the database server.
-<code>138@@</code>
+<code>
+CREATE OR REPLACE TRIGGER log_server_errors
+  AFTER SERVERERROR
+  ON DATABASE
+DECLARE
+          v_sqltext VARCHAR2(1000);
+          nl_sqltext ora_name_list_t;
+  BEGIN
+          -- Capture entire error text
+          FOR i IN 1 .. ora_sql_txt(nl_sqltext) LOOP
+            v_sqltext := v_sqltext || nl_sqltext(i);
+          END LOOP;
+          INSERT INTO STATS$ERROR_LOG
+          (err_dt, db_user, msg_stack, sqltxt)
+          VALUES
+          (systimestamp,
+           sys.login_user,
+           dbms_utility.format_error_stack, v_sqltext);
+  END log_server_errors;
+/
+</code>
-=====ORA-19809: limit exceeded for recovery files=====
+==== ORA-19809: limit exceeded for recovery files ====
-<code>139@@</code>
+<code>
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/15/2013 10:10:59
+ORA-19809: limit exceeded for recovery files
+ORA-19804: cannot reclaim 274716160 bytes disk space from 1468006400 limit
+</code>
-<code>140@@</code>
+<code>
+    ORA-19809: limit exceeded for recovery files
+    Cause: The limit for recovery files specified by the db_recovery_file_dest_size was exceeded.
+    Action: The error is accompanied by 19804. See message 19804 for further details
+    ORA-19804: cannot reclaim string bytes disk space from string limit
+    Cause: Oracle cannot reclaim disk space of specified bytes from the DB_RECOVERY_FILE_DEST_SIZE limit.
+    Action: There are five possible solutions:
+) Take frequent backup of recovery area using RMAN.
+) Consider changing RMAN retention policy.
+) Consider changing RMAN archivelog deletion policy.
+) Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
+) Delete files from recovery area using RMAN.
+</code>
 The solution here is to increase the value of db_recovery_file_dest_size but it might also indicate a problem with tapes if the archivelogs are being backed up to tape via tdpo.
-<code>141@@</code>
+<code>
+SYS@DN7> alter system set db_recovery_file_dest_size=4000M scope=both;
+System altered.
+</code>
 Looking into int further, saw RMAN parameters missing...
-<code>142@@</code>
+<code>
-=====RMAN-00554: initialization of internal recovery manager package failed=====
+CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
+CONFIGURE BACKUP OPTIMIZATION OFF;
+CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
+CONFIGURE CONTROLFILE AUTOBACKUP ON;
+CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO 'LOG_%d_controlfile_%F.rman';
+CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
+</code>
+==== RMAN-00554: initialization of internal recovery manager package failed ====
 Getting this error after migrating a database from one machine to another and attaching to a nice new version 12c catalog on the new server
-<code>143@@</code>
+<code>
-We cannot connect to the catalog via rman but there is no problem if connecting via SQL*Plus!<br />
+Recovery Manager: Release 11.2.0.2.0 - Production on Sat Jun 21 13:16:24 2014
+Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
+connected to target database: SPMSUPP1 (DBID=3026014394)
+RMAN-00571: ===========================================================
+RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
+RMAN-00571: ===========================================================
+RMAN-00554: initialization of internal recovery manager package failed
+RMAN-04004: error from recovery catalog database: ORA-01017: invalid username/password; logon denied
+</code>
+We cannot connect to the catalog via rman but there is no problem if connecting via SQL*Plus!\\
 Solution: relink the rman executable.
-=====Using AIX commands genld, genkld and slibclean to avoid library file locking errors (libjox) (Doc ID 739963.1)=====
+==== Using AIX commands genld, genkld and slibclean to avoid library file locking errors (libjox) (Doc ID 739963.1) ====
-  * [[https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=206851390914720&id=739963.1&_afrWindowMode=0&_adf.ctrl-state=e0pq23tou_4|Metalink]]
+  *  [[https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=206851390914720&id=739963.1&_afrWindowMode=0&_adf.ctrl-state=e0pq23tou_4|Metalink]]
-When installing interim (one-off) patches, CPU patches or patchsets, you may encounter some errors with regards to files being locked or oui/opatch being unable to copy files<br />
+When installing interim (one-off) patches, CPU patches or patchsets, you may encounter some errors with regards to files being locked or oui/opatch being unable to copy files\\
-even though the databases, listeners and all other Oracle processes associated with the ORACLE_HOME to be patched were stopped.<br />
+even though the databases, listeners and all other Oracle processes associated with the ORACLE_HOME to be patched were stopped.\\
-This could be as result of a process which requires termination or an additional file needing to be unloaded from the system cache.<br />
+This could be as result of a process which requires termination or an additional file needing to be unloaded from the system cache.\\
 Run following commands to clean up...
-<code>144@@</code>
+<code>
+genld -l | grep <ORACLE_HOME>
+</code>
 If genld returns data then a currently executing process has something open in the ORACLE_HOME directory, therefore terminate the process as required/recommended.
-<code>145@@</code>
+<code>
+genkld | grep <ORACLE_HOME>
+</code>
 If the genkld command  returns a  list of shared objects currently loaded onto the OS system cache then please remove the entries from the OS system cache by running the slibclean command as root user:
-<code>146@@</code>
+<code>
-=====ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout=====
+/usr/sbin/slibclean
-This seems to be related to orphaned Datapump tables. Generally seems to be a problem when stats jobs are running during the night (dbms_stats)<br />
+</code>
-  * Check no datapump jobs are running
+==== ORA-1652: unable to extend temp segment by 512 in tablespace TEMP_SYSTEM ====
-<code>147@@</code>
+Noticed this after patching. datapatch was failing with these errors in the alertlog
-  * Find orphaned tables
+<code>
-<code>148@@</code>
+ORA-1652: unable to extend temp segment by 512 in tablespace TEMP_SYSTEM
-Check that  any rows seen are actually external tables
+Unable to obtain current patch information due to error: 20001, ORA-20001: Latest xml inventory is not loaded into table
-<code>149@@</code>
+ORA-06512: at "SYS.DBMS_QOPATCH", line 2327
-  * Drop the tables if they are not currently being used (no datapump jobs running)
+ORA-06512: at "SYS.DBMS_QOPATCH", line 854
-<code>150@@</code>
+ORA-06512: at "SYS.DBMS_QOPATCH", line 937
-=====ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below=====
+ORA-06510: PL/SQL: unhandled user-defined exception
+ORA-06512: at "SYS.DBMS_QOPATCH", line 932
+ORA-29913: error in executing ODCIEXTTABLEFETCH callout
+ORA-01652: unable to extend temp segment by 512 in tablespace TEMP_SYSTEM
+ORA-06512: at "SYS.DBMS_QOPATCH", line 919
+ORA-06512: at "SYS.DBMS_QOPATCH", line 2286
+ORA-06512: at "SYS.DBMS_QOPATCH", line 817
+ORA-06512: at "SYS.DBMS_QOPATCH", line 2309
+</code>
+There were already 2 tempfiles in TEMP_SYSTEM so it was unlikely to need another. While attempting to rebuild the temp tablespaces, I noticed the 2 tempfiles in TEMP_SYSTEM were offline!!
+<code>
+select name,status from v$tempfile;
+/cln/acc/ora_data/adsa/ts_precise_tmp.dbf              ONLINE
+/cln/acc/ora_data/adsa/temp_app/temp_app_01.dbf        ONLINE
+/cln/acc/ora_data/adsa/temp_app/temp_app_02.dbf        ONLINE
+/cln/acc/ora_data/adsa/temp_app/temp_app_03.dbf        ONLINE
+/cln/acc/ora_data/adsa/temp_app/temp_app_04.dbf        ONLINE
+/cln/acc/ora_data/adsa/temp_app/temp_app_05.dbf        ONLINE
+/cln/acc/ora_data/adsa/temp_app/temp_app_06.dbf        ONLINE
+/cln/acc/ora_data/adsa/temp_system/temp_system_01.dbf  OFFLINE
+/cln/acc/ora_data/adsa/temp_system/temp_system_02.dbf  OFFLINE
+</code>
+<code>
+alter database tempfile '/cln/acc/ora_data/adsa/temp_system/temp_system_01.dbf' online;
+alter database tempfile '/cln/acc/ora_data/adsa/temp_system/temp_system_02.dbf' online;
+</code>
+Retry the datapatch and we're all good again!
+==== ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ====
+  *  [[https://support.oracle.com/epmos/faces/DocumentDisplay?id=1274653.1|ORA-20011 ORA-29913 and ORA-29400 with Associated KUP-XXXXX Errors from DBMS_STATS.GATHER_STATS_JOB (Doc ID 1274653.1)]]
+This seems to be related to orphaned dictionary tables (in this particular case, Datapump). Generally seems to be a problem when stats jobs are running during the night (dbms_stats)\\
+  *  Check no datapump jobs are running
+<code>
+select * from dba_datapump_jobs;
+</code>
+  *  Find orphaned tables
+<code>
+set lines 2000
+select owner
+,      object_name
+,      object_type
+,      status
+,      to_char(created,'dd-mon-yyyy hh24:mi:ss') created
+,      to_char(last_ddl_time,'dd-mon-yyyy hh24:mi:ss') last_ddl_time
+from   dba_objects
+where  1=1
+and    object_name like 'ET$%';
+</code>
+Check that any rows seen are actually external tables
+<code>
+select owner
+,      table_name
+,      default_directory_name
+,      access_type
+from   dba_external_tables
+order  by 1,2
+</code>
+  *  Drop the tables if they are not currently being used (no datapump jobs running)
+<code>
+drop table &&owner..&table_name;
+</code>
+Another case, in alertlog we see
+<code>
+Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
+Wed Apr 15 23:00:09 2020
+DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.
+Errors in file /cln/exp/ora_bin2/app/oracle/diag/rdbms/grayp/grayp/trace/grayp_j001_20123338.trc:
+ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
+ORA-29400: data cartridge error
+KUP-04040: file ODGRAYDON.txt in EXTSQL_GRAYDON not found
+Wed Apr 15 23:23:02 2020
+End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
+</code>
+In the mentioned tracefile, we see
+<code>
+*** 2020-04-15 23:00:09.829
+DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"LBK"','"GRAYDON_BULK"','""', ...)
+DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
+ORA-29400: data cartridge error
+KUP-04040: file ODGRAYDON.txt in EXTSQL_GRAYDON not found
+*** 2020-04-15 23:00:09.872
+DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"LBK"','"GRAYDON_BULK_C"','""', ...)
+DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
+ORA-29400: data cartridge error
+KUP-04040: file ODGRAYDON_C.txt in EXTSQL_GRAYDON not found
+</code>
+We see in this example that the gather_stats job is trying to access lbk.graydon_bulk which we can also see is an external table referencing ODGRAYDON.txt in whatever directory EXTSQL_GRAYDON is pointing to.\\
+Checking that directory on the filesystem, those files are indeed missing.\\
+The solution would be to drop those dictionary tables that refer to the files.
+<code>
+drop table lbk.graydon_bulk;
+drop table lbk.graydon_bulk_c;
+</code>
+==== Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME to database unique name. ====
+When trying to start the Grid agent with
+<code>
+emctl start agent
+</code>
+that error is seen. Try checking the PATH variable! IS the agent home at the beginning or is there some other home before it? Se tthe agent home at the beginning of the PATH and try again.
+<code>
+(0) agent oracle@hn5115:/home/oracle> emctl start agent
+Oracle Enterprise Manager Cloud Control 13c Release 3
+Copyright (c) 1996, 2018 Oracle Corporation.  All rights reserved.
+Starting agent ............................................... started.
+</code>
+==== EM13c : Agent Causes High CPU And Hangs The Server (Doc ID 2213714.1) ====
+No specific errors but alerts generated by O/S team showing high CPU usage and the java process for the agent consistently shows on top of topas listing.\\
+Solution is to modify a value in emd.properties file, add a line to s_jvm_options.opt and restart the agent.
+  * Set the environment to the agent
+  * Find the name of the properties file
+<code>
+vi $(awk -F':' '{print $2 "/sysman/config/emd.properties"}' /etc/oragchomelist)
+</code>
+  * Back this file up
+  * Modify agentJavaDefines=-Xmx128M to agentJavaDefines=-Xmx1024M
+  * Find the name of the options file
+<code>
+vi $(awk -F':' '{print $2 "/sysman/config/s_jvm_options.opt"}' /etc/oragchomelist)
+</code>
+  * Back this file up
+  * Add -XX:ParallelGCThreads=8
+  * Restart the agent
+<code>
+emctl stop agent
+emctl start agent
+</code>
+==== EM 13.4: OMS Java Process Consuming High CPU and Causing Console Slowness / OMS Hung Intermittently (Doc ID 2702019.1) ====
+OMS java process uses high CPU due to java 1.8 using a lot higher codeCache than previous versions.\\
+Solution os to increase the codeCacheSize from 100M to at least 512M and restart the oms.
+  * Set the environment to the oms
+  * Check the current value
+<code>
+emctl get property -name JAVA_EM_ARGS
+</code>
+can also be seen in a ps listing
+<code>
+ps -ef | grep EMGC_OMS | grep ReservedCodeCacheSize
+</code>
+  * Set a higher value
+<code>
+emctl set property -name JAVA_EM_ARGS -value "-XX:ReservedCodeCacheSize=512M"
+</code>
+  * Restart the oms
+<code>
+emctl stop oms -all
+emctl start oms
+</code>
+==== ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ====
+  *  [[https://dbatricksworld.com/ora-01194-file-1-needs-more-recovery-to-be-consistent/|dbatricksworld.com]]
 Database is restored and now we want to recover it but we see this...
-<code>151@@</code>
+<code>
-This means that although we have restored the database, we are still missing information to complete the recovery. This information is contained in the archived redo logs which must also be restored (from tape if they are no longer on disk)<br />
+Starting recover at 24-JUL-15 11:26:19
-We can see here that we need an archive log with sequence number 69763. But what is the file called and where should it be restored to?<br />
+starting media recovery
+unable to find archive log
+archive log thread=1 sequence=69763
+Oracle Error:
+ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
+ORA-01194: file 1 needs more recovery to be consistent
+ORA-01110: data file 1: '/oracle/TRSCRP1/oradata2/system01.dbf'
+released channel: c1
+</code>
+This means that although we have restored the database, we are still missing information to complete the recovery. This information is contained in the archived redo logs which must also be restored (from tape if they are no longer on disk)\\
+We can see here that we need an archive log with sequence number 69763. But what is the file called and where should it be restored to?\\
 For this we can go to SQL*Plus and get the database to tell us
-<code>152@@</code>
+<code>
+recover database using backup controlfile until cancel;
+</code>
 and cancel straight away.
-<code>153@@</code>
+<code>
-Now we see that we need to find a file called arch_TRSCRP1_1_69763.arc and put it in the directory /oracle/TRSCRP1/archive.<br />
+ORA-00279: change 13796376414661 generated at 07/14/2015 13:20:07 needed for
-This will continue up until the time specified for the recover so restore a whole bunch of them.<br />
+thread 1
-Repeat the "recover database using backup controlfile until cancel;" command and type AUTO or Enter until you've got to where you need to be.<br />
+ORA-00289: suggestion : /oracle/TRSCRP1/archive/arch_TRSCRP1_1_69763.arc
+ORA-00280: change 13796376414661 for thread 1 is in sequence #69763
+Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+cancel
+ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
+ORA-01194: file 1 needs more recovery to be consistent
+ORA-01110: data file 1: '/oracle/TRSCRP1/oradata2/system01.dbf'
+ORA-01112: media recovery not started
+</code>
+Now we see that we need to find a file called arch_TRSCRP1_1_69763.arc and put it in the directory /oracle/TRSCRP1/archive.\\
+This will continue up until the time specified for the recover so restore a whole bunch of them.\\
+Repeat the "recover database using backup controlfile until cancel;" command and type AUTO or Enter until you've got to where you need to be.\\
 Type CANCEL when point is reached and
-<code>154@@</code>
+<code>
+alter database open resetlogs;
+</code>
+=== ORA-01194: file 1 needs more recovery to be consistent ===
+Found another method online but I cannot see why it works...
+<code>
+SQL> shutdown immediate
+ORA-01109: database not open
+Database dismounted.
+ORACLE instance shut down.
+SQL> startup mount
+ORACLE instance started.
+Total System Global Area  530288640 bytes
+Fixed Size                  2131120 bytes
+Variable Size             310381392 bytes
+Database Buffers          209715200 bytes
+Redo Buffers                8060928 bytes
+Database mounted.
+SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE;
+SQL> ALTER SYSTEM SET undo_management=MANUAL SCOPE = SPFILE;
+SQL> shutdown immediate
+ORA-01109: database not open
+Database dismounted.
+ORACLE instance shut down.
+SQL> startup mount
+ORACLE instance started.
+Total System Global Area  530288640 bytes
+Fixed Size                  2131120 bytes
+Variable Size             310381392 bytes
+Database Buffers          209715200 bytes
+Redo Buffers                8060928 bytes
+Database mounted.
+SQL> alter database open resetlogs;
+Database altered.
+SQL> CREATE UNDO TABLESPACE undo1 datafile '<ora_data_path>undo1_1.dbf' size 200m autoextend on maxsize unlimited;
+Tablespace created.
+SQL> ALTER SYSTEM SET undo_tablespace = undo1 SCOPE=spfile;
+System altered.
+SQL> alter system set undo_management=auto scope=spfile;
+System altered.
+SQL> shutdown immediate
+SQL> startup
+</code>