EXP-00008, ORA-04063, ORA-06508, EXP-00083 PL/SQL: could not find program unit being called: "WMSYS.LTUTIL"

PROBLEM:
The following errors were thrown during export:

. exporting dimensions
. exporting post-schema procedural objects and actions
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "WMSYS.LTUTIL" has errors
ORA-06508: PL/SQL: could not find program unit being called: "WMSYS.LTUTIL"
ORA-06512: at "WMSYS.LT_EXPORT_PKG", line 1688
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling WMSYS.LT_EXPORT_PKG.schema_info_exp
. exporting statistics
Export terminated successfully with warnings.

CAUSES:

1. There are invalid objects in the WORKSPACE, XDB schemas.
2. Absence of execute privilege on UTL_FILE package from PUBLIC.
 
SOLUTION:

CAUSE 1: Connect as SYS and run $ORACLE_HOME/rdbms/admin/owminst.plb to allow the LT_EXPORT_PKG to be validated.

CAUSE 2:

1. Grant execute privilege on SYS.UTL_FILE package to WMSYS user.

SQL> grant execute on SYS.UTL_FILE to WMSYS;

2. check for invalid objects in the WMSYS schema using following query,

 SQL> select object_name,object_type,status from dba_objects where status='INVALID' and owner='WMSYS';

3. Run script $ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Run the export again.

Oracle RAC 11gR2: Listener INTERMEDIATE status with "Not All Endpoints Registered"

Problem:

$GRID_HOME/bin/crsctl stat res -t on one RAC node displays the error on node1 ----------------------------------------------------------------------------------------------------- NAME TARGET STATE SERVER STATE_DETAILS ----------------------------------------------------------------------------------------------------- Local Resources ----------------------------------------------------------------------------------------------------- ora.LISTENER.lsnr ONLINE INTERMEDIATE node1 Not All Endpoints Registered ONLINE ONLINE node2


Cause:
Listener was started from $ORACLE_HOME instead of $GRID_HOME

 Solution:

1. Stop currently running local listener from $ORACLE_HOME.

$ORACLE_HOME/bin/lsnrctl stop

2.  Stop SCAN listeners currently running on node1.

$GRID_HOME/bin/crsctl stop res ora.LISTENER.lsnr -n node1
$GRID_HOME/bin/crsctl stop res ora.LISTENER_SCAN1.lsnr -n node1
$GRID_HOME/bin/crsctl stop res ora.LISTENER_SCAN2.lsnr -n node1

3. Start local listener from $GRID_HOME of node1

 $GRID_HOME/bin/crsctl start res ora.LISTENER.lsnr -n node1

4. Start listeners from GRID_HOME.

$GRID_HOME/bin/crsctl start res ora.LISTENER_SCAN1.lsnr -n node1
$GRID_HOME/bin/crsctl start res ora.LISTENER_SCAN2.lsnr -n node1

5. Check CRS status now.

$GRID_HOME/bin/crsctl stat res -t Status should be online now. ----------------------------------------------------------------------------------------------------- NAME TARGET STATE SERVER STATE_DETAILS ----------------------------------------------------------------------------------------------------- Local Resources ----------------------------------------------------------------------------------------------------- ora.LISTENER.lsnr ONLINE ONLINE node1 ONLINE ONLINE node2

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

This error was faced when one customer was trying to load data into an Oracle 12 database. From Oracle Database 12c, there is a new hard limit set for the size of PGA taken up by a particular instance. The parameter is called PGA_AGGREGATE_LIMIT. Read up on this from the Oracle 12c reference. oerr utility shows this: $oerr ora 04036 ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT Cause: Private memory across the instance exceeded the limit specified in the PGA_AGGREGATE_LIMIT initialization parameter. The largest sessions using Program Global Area (PGA) memory were interrupted to get under the limit. Action: Increase the PGA_AGGREGATE_LIMIT initialization parameter or reduce memory usage. $ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Wed May 27 12:38:28 2015 Copyright (c) 1982, 2013, 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> show parameter aggregate NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_limit big integer 6000M pga_aggregate_target big integer 0 Resolution: Set the parameter to 0 (pre-12c behaviour) SQL> alter system set pga_aggregate_limit=0 scope=both; System altered. Another option is to increase the value if there is enough physical memory available to the database. This resolved the issue and the customer was able to continue loading.

online datafile move in Oracle Database 12c

This is such a cool feature which will help space management both in databases using ASM and filesystem. The challenge: Need to relocate a tablespace to another filesystem or diskgroup. This had to involve some down time in the past, however it can be done online with this new feature. Syntax for this is available in Oracle documentation and several websites. NB: This feature does not work for temp files.

ORA-01114: IO error writing block to file name block

Environment: Oracle Database 12.1.0.1.0 OS: AIX 6.1 The following error was reported in the alert log of one of my databases when a user tried to run a select statement. ORA-01114: IO error writing block to file (block # ) Fri May 15 10:22:00 2015 Errors in file /ORACLE_HOME/app/oracle/diag/rdbms/xxxxxx/XXXXXX/trace/XXXXXX_m001_43188288.trc: ORA-01114: IO error writing block to file (block # ) "oerr ora 01114" shows the following: 01114, 00000, "IO error writing block to file %s (block # %s)" // *Cause: The device on which the file resides is probably offline. If the // file is a temporary file, then it is also possible that the device // has run out of space. This could happen because disk space of // temporary files is not necessarily allocated at file creation time. // *Action: Restore access to the device or remove unnecessary files to free // up space. I looked at the file systems and found one which is 100% used. Checking further revealed that the file system held data files and the database temp file. The datafiles were configured with AUTOEXTENSIBLE OFF and checks on tablespace usage revealed that all tablespaces were below 75% usage threshold. However, since the user was trying to run a select statement which will involve sorting, it became obvious that this has to do with the temporary tablespace. In my case I had another filesystem that had enough free space so I decided to migrate my default temporary tablespace to a new location. Oracle 12c has a new feature where you can migrate datafiles online, however this beautiful feature does not work for temp files! STEPS TO CREATE A NEW TEMPORARY TABLESPACE. ============================================== STEP 1. Create a new temporary tablespace (e.g. TEMP2). SQL> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/u03/XXXXXX/oradata/XXXXXX/temp/temp01.dbf' size 30g autoextend off; STEP 2. Change default temporary tablespace (assume it was TEMP)to the newly created one (TEMP2). SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2; STEP 3. Check to see that no user sessions are using the original temporary tablespace. If there are any, identify and kill the sessions ONLY if it will not impact production activities. i. Get session number from the V$SORT_USAGE view : SQL> SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE; ii. Get corresponding session ID from the V$SESSION view: This can be done using SESSION_NUM or SESSION_ADDR from the results gotten from the above query. SQL> SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM; SQL> SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR; iii. Kill identified Session(s) with IMMEDIATE option: SQL> ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE; STEP 4. Drop original temporary tablespace (TEMP in this case) SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES; STEP 5. Recreate a new/larger temporary tablespace (named TEMP) SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u03/XXXXXX/oradata/XXXXXX/temp/temp001.dbf' size 30g autoextend off; STEP 6. Move default temporary tablespace back to TEMP. SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP; STEP 7. Drop TEMP2 temporary tablespace. NB: Dropping TEMP2 tablespace may hand due to usage by a session, so you must check and kill any sessions currently using the temporary tablespace we are about to drop like was done in step 3 above. SQL> SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE; USERNAME SESSION_NUM SESSION_ADDR ------------------------------ ----------- ---------------- SYS 17639 070001004FE710B0 SQL> SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=17639; SID SERIAL# STATUS ---------- ---------- -------- 367 17639 ACTIVE SQL> alter system kill session '367,17639' immediate; System altered. SQL> SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE; no rows selected SQL> DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES; CONCLUSION: The steps stated above helped in resolving the issue and gave joy to the customer!

ORA-00230: operation disallowed: snapshot control file enqueue unavailable

This error encountered during archivelog backup of a database. More details of error below: Starting backup at 27-APR-15 channel CH01: starting full datafile backup set channel CH01: specifying datafile(s) in backup set waiting for snapshot control file enqueue waiting for snapshot control file enqueue waiting for snapshot control file enqueue waiting for snapshot control file enqueue waiting for snapshot control file enqueue waiting for snapshot control file enqueue waiting for snapshot control file enqueue waiting for snapshot control file enqueue 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 released channel: CH01 released channel: CH02 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of backup command on CH01 channel at 04/27/2015 04:00:14 ORA-00230: operation disallowed: snapshot control file enqueue unavailable Cause: One process is holding an enqueue on the control file when this backup attempt was made. Two options are: 1. Wait for the enqueue holding session/process to complete. 2. Locate and terminate the holder if you can afford to. For option 2. Use the sql below to locate the process: SELECT s.sid, username, program, module, action, logon_time, 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 can use operating system "kill -9" command to terminate the identified process and then re-initiate the backup. For windows, use "orakill" utility to terminate the specific thread. orakill sid thread_id sid: instance ID. thread_id: SPID from above. reference: http://www.arcserve-knowledgebase.com

ORA-01012: not logged on

The following happened when a user connects to an already running database: $ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 1 08:13:58 2015 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected. SQL> SQL> SQL> SQL> select name from v$database; select name from v$database * ERROR at line 1: ORA-01012: not logged on Process ID: 0 Session ID: 0 Serial number: 0 Cause: This is usually happens to an heavily loaded database. Solution: The following options will help resolve the challenge. 1. Kill Oracle processes> #ps -ef |grep ora_smon* #kill -9 smon_process_id $sqlplus / as sysdba SQL> startup 2. Restart the database server and bring up the database afterwards. 3. Stop the application server and stop listener to prevent new incoming connections. This will release sessions and allow new connections to the database. Additionally, it is advised that the "processes" parameter be increased on the database. SQL> show parameter processes SQL> alter system set processes=new_value scope spfile; SQL> shutdown (immediate) SQL> startup SQL> show parameter processes