Cause: Pointer to hstdef extension in hstdef is null.
Action: Report as a bug
If ORA-01041 is thrown, an internal error has occurred in which the pointer to the hstdef extension in hstdef is null.
In Oracle, many times ORA-01041 is thrown after either startup or shutdown. If you are using SQL*Plus, you will have to reconnect to the database or start a new SQL*Plus session. The ORA-01041 has been known to be caused by a bug which des not seem to allow a shutdown immediately followed by a startup in a congruent SQL*Plus session.
To resolve ORA-01041, you should report the error as a bug. In the case of ORA-01041 as a response to performing shutdown and startup, you can use the ipcrm command and remove allocated shared memory segments.
SELECT x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl,ksppstdf,
DECODE(BITAND(ksppiflg/256,1),1,'TRUE','FALSE'),
DECODE(BITAND(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE'),
DECODE(BITAND(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE'),
DECODE(BITAND(ksppstvf,2),2,'TRUE','FALSE'),
ksppdesc
FROM x$ksppi x,
x$ksppcv y
WHERE (x.indx = y.indx)
--AND x.indx =1178
/*and (translate(ksppinm,'_','#') not like '#%'
OR (TRANSLATE(ksppinm,'_','#') LIKE '#%'AND ksppstdf = 'TRUE'))*/
ORDER BY 3;
Reason :The installer could not find the correct files because it was expecting them to be in the one folder and not two different one. Solution: windows:
Copy the contents of the components folder into the one components folder, cleaned up the failed install and then re-ran the install operation. This will solve the problem.
Got the following error today when trying to execute full export backup command: exp system/sys file=full_exp2.dmp log=full_exp2.log full=y consistent=y statistics=none buffer=10000000 EXP-00008: ORACLE error 25153 encountered ORA-25153: Temporary Tablespace is Empty EXP-00083: The previous problem occurred when calling SYS.LT_EXPORT_PKG.system_info_exp EXP-00008: ORACLE error 25153 encountered ORA-25153: Temporary Tablespace is Empty ORA-06512: at "SYS.DBMS_LOB", line 443 ORA-06512: at "SYS.DBMS_SCHED_MAIN_EXPORT", line 571 ORA-06512: at "SYS.DBMS_SCHED_WINDOW_EXPORT", line 14 ORA-06512: at line 1 EXP-00085: The previous problem occurred when calling SYS.DBMS_SCHED_WINDOW_EXPORT.create_exp for object 8879 EXP-00008: ORACLE error 25153 encountered ORA-25153: Temporary Tablespace is Empty ORA-06512: at "SYS.DBMS_LOB", line 443 ORA-06512: at "SYS.DBMS_SCHED_MAIN_EXPORT", line 571 ORA-06512: at "SYS.DBMS_SCHED_WINDOW_EXPORT", line 14 ORA-06512: at line 1 EXP-00085: The previous problem occurred when calling SYS.DBMS_SCHED_WINGRP_EXPORT.create_exp for object 8881 . exporting pre-schema procedural objects and actions . exporting cluster definitions EXP-00056: ORACLE error 25153 encountered ORA-25153: Temporary Tablespace is Empty EXP-00056: ORACLE error 25153 encountered ORA-25153: Temporary Tablespace is Empty EXP-00000: Export terminated unsuccessfully Solution:
First step is to check for temp tablespace and assosiation with users.
select username, temporary_tablespace, account_status from dba_users where username not like '%SYS%';
If
temporary_tablespace exists (TEMP) move to step2 to check datafiles assosiated with the tablespace as below:
Issue No data File associated with temp tablespace: SQl>Select file_name from dba_temp_files; No row selected
As you can see there is no data file associated with Temp tablespace;this is the reason of the issue.
To solve the issue simply add a datafile to the temp tablespace using the below command:
package binutils-2.15.92.0.2-15 (which is newer than binutils-2.15.92.0.2-13.0.0.0.2) is already installed
file /usr/bin/addr2line from install of binutils-2.15.92.0.2-13.0.0.0.2 conflicts with file from package binutils-2.15.92.0.2-15 file /usr/bin/ar from install of binutils-2.15.92.0.2-13.0.0.0.2 conflicts with file from package binutils-2.15.92.0.2-15 file /usr/bin/as from install of binutils-2.15.92.0.2-13.0.0.0.2 conflicts with file from package binutils-2.15.92.0.2-15 file /usr/bin/gprof from install of binutils-2.15.92.0.2-13.0.0.0.2 conflicts with file from package binutils-2.15.92.0.2-15 file /usr/bin/ld from install of binutils-2.15.92.0.2-13.0.0.0.2 conflicts with file from package binutils-2.15.92.0.2-15 file /usr/bin/nm from install of binutils-2.15.92.0.2-13.0.0.0.2 conflicts with file from package binutils-2.15.92.0.2-15 file /usr/lib64/libbfd.a from install of binutils-2.15.92.0.2-13.0.0.0.2 conflicts with file from package binutils-2.15.92.0.2-15 file /usr/lib64/libiberty.a from install of binutils-2.15.92.0.2-13.0.0.0.2 conflicts with file from package binutils-2.15.92.0.2-15 file /usr/lib64/libopcodes-2.15.92.0.2.so from install of binutils-2.15.92.0.2-13.0.0.0.2 conflicts with file from package binutils-2.15.92.0.2-15 file /usr/lib64/libopcodes.a from install of binutils-2.15.92.0.2-13.0.0.0.2 conflicts with file from package binutils-2.15.92.0.2-15
Cause: gcc-3.4.3-9.EL4.x86_64.rpm is missing
Below are the steps used and errors i faced while installing the rpm gcc-3.4.3-9.EL4.x86_64.rpm
: ##### To install
gcc-3.4.3-9.EL4.x86_64.rpm ###### [root@SUMITDBSERVER rpm]# rpm -ivh gcc-3.4.3-9.EL4.x86_64.rpm
error: Failed dependencies:
cpp 3.4.3-9.EL4 is needed by gcc-3.4.3-9.EL4.x86_64
glibc-devel-2.3.4-2.13.x86_64.rpm is needed by gcc-3.4.3-9.EL4.x86_64
[root@SUMITDBSERVER rpm]# rpm -ivh glibc-devel-2.3.4-2.13.x86_64.rpm
warning: glibc-devel-2.3.4-2.13.x86_64.rpm: V3 DSA signature: NOKEY, key ID a704 error: Failed dependencies:
glibc-headers is needed by glibc-devel-2.3.4-2.13.x86_64
glibc-headers = 2.3.4-2.13 is needed by glibc-devel-2.3.4-2.13.x86_64
Suggested resolutions:
glibc-headers-2.3.4-2.13.x86_64.rpm
[root@SUMITDBSERVER rpm]# rpm -ivh glibc-headers-2.3.4-2.13.x86_64.rpm
warning: glibc-headers-2.3.4-2.13.x86_64.rpm: V3 DSA signature: NOKEY, key ID a7 error: Failed dependencies:
kernel-headers is needed by glibc-headers-2.3.4-2.13.x86_64
kernel-headers >= 2.2.1 is needed by glibc-headers-2.3.4-2.13.x86_64
Suggested resolutions:
glibc-kernheaders-2.4-9.1.98.EL.x86_64.rpm [root@SUMITDBSERVER rpm]# rpm -ivh glibc-kernheaders-2.4-9.1.98.EL.x86_64.rpm
warning: glibc-kernheaders-2.4-9.1.98.EL.x86_64.rpm: V3 DSA signature: NOKEY, ke
Preparing... ########################################### [100%]
1:glibc-kernheaders ########################################### [100%] [root@SUMITDBSERVER rpm]# rpm -ivh glibc-headers-2.3.4-2.13.x86_64.rpm warning: glibc-headers-2.3.4-2.13.x86_64.rpm: V3 DSA signature: NOKEY, key ID a7
Preparing... ########################################### [100%]
1:glibc-headers ########################################### [100%]
[root@SUMITDBSERVER rpm]# rpm -ivh glibc-devel-2.3.4-2.13.x86_64.rpm
warning: glibc-devel-2.3.4-2.13.x86_64.rpm: V3 DSA signature: NOKEY, key ID a704
Preparing... ########################################### [100%]
1:glibc-devel ########################################### [100%] [root@SUMITDBSERVER rpm]# rpm -ivh gcc-3.4.3-9.EL4.x86_64.rpm
warning: gcc-3.4.3-9.EL4.x86_64.rpm: V3 DSA signature: NOKEY, key ID a7048f8d error: Failed dependencies:
cpp = 3.4.3-9.EL4 is needed by gcc-3.4.3-9.EL4.x86_64 [root@SUMITDBSERVER rpm]# rpm -q cpp
cpp-3.4.4-2 [root@SUMITDBSERVER rpm]# rpm -ivhcpp-3.4.3-9.EL4.x86_64.rpm
warning:
cpp-3.4.3-9.EL4.x86_64.rpm
: V3 DSA signature: NOKEY, key ID 192a7d7d
Preparing... ########################################### [100%]
file /usr/bin/cpp from install of
cpp-3.4.3-9.EL4.x86_64.rpm
conflicts with file from package cpp-3.4.4-2
file /usr/libexec/gcc/x86_64-redhat-linux/3.4.3/cc1 from install of
cpp-3.4.3-9.EL4.x86_64.rpm
conflicts with file from package cpp-3.4.4-2
file /usr/share/man/man1/cpp.1.gz from install of
cpp-3.4.3-9.EL4.x86_64.rpm
conflicts with file from package cpp-3.4.4-2 Issue: The problem here is that we had cpp-3.4.4-2 RPM installed on the system but oracle x86_64 needs cpp-3.4.3-9.EL4.x86_64.rpm .When we are trying to install the cpp-3.4.3-9 RPM it is conflicting with the existing files of the cpp package:
Solution: Replace the files of cpp-3.4.3-9.EL4 with the
cpp-3.4.3-9.EL4.x86_64.rpm files forcefully.
rpm -Uvh --replacefiles cpp-3.4.3-9.EL4.x86_64.rpm Now we can install gcc-3.4.3-9.EL4.x86_64.rpm as all the dependent RPMS have been successfully installed. [root@SUMITDBSERVER rpm]# rpm -ivh gcc-3.4.3-9.EL4.x86_64.rpm Preparing... ########################################### [100%] 1:gcc-3.4.3-9 ########################################### [100%]
Undo data can be divided into 3 parts."Active" undo is undo data that might be needed to roll back transactions in progress in progress. Active undo data can never be overwritten,until the transaction completes. "Expired" undo is undo data from committed transaction that oracle is no longer obliged to store-though it might be needed for consistency reads,if there are any long running queries in progress.This data can be overwritten if oracle needs the space for another active transaction . " Unexpired "extents are non-active extents that still honour UNDO_RETENTION. The transactions belonging to these undo extents are committed, but the retention time has not passed: You still want/need these for Read Consistency! When the Undo mechanism requires more extents for ACTIVE extents, it is allowed to steal UNEXPIRED extents when there are no EXPIRED extents left for reuse and it can not allocate more free extents (autoextend maxsize reached or fixed tablespace size). One can check the steal-count in UNXPSTEALCNT in V$UNDOSTAT. You will get ‘ORA-01555 snapshot too old‘ errors if no Read Consistency information for a query is available. The SSOLDERRCNT in V$UNDOSTAT will show a count of these errors.
The undo tablespace follows two simple principles:
#1.There should always be sufficient undo space to allow all transactions to continue.It requires that your undo tablespace be large enough to accommodate the worst case for undo demand.It should have enough space allocated for the worst case--The peak usage of active undo data generated by your transaction workload.
#2.There should always be sufficient undo data for all queries in progress to succeed.It requires that there be additional space in the undo tablespace to store the expired undo data that might be needed for the read consistency,so that long running queries will not fail with Oracle error ora-1555. If a query fails on consistent read with "Snapshot too old", it means that the query hit a block that had been changed since the query started,but when it went to the undo segment to find the pre-updated version of the data,that bit of undo data had been overwritten (Only expired undo can be overwritten.Oracle can't overwrite Active undo data).
If the transaction runs out of undo space,it will fail with the error ORA-30036,unable to extend segments in undo tablespace.The statement that hits the problem is rolled back ,but rest of the transaction remains intact and uncommitted.The algorithm that assigns space within the undo tablespace to undo segments means that this error condition will arise only if the undo tablspace is absolutely full on unexpired undo data(so cannot be overwritten).
Undo tablespace and retension recommendation script
SET SERVEROUTPUT ON
SET LINES 600
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';
DECLARE
v_analyse_start_time DATE := SYSDATE - 7;
v_analyse_end_time DATE := SYSDATE;
v_cur_dt DATE;
v_undo_info_ret BOOLEAN;
v_cur_undo_mb NUMBER;
v_undo_tbs_name VARCHAR2(100);
v_undo_tbs_size NUMBER;
v_undo_autoext BOOLEAN;
v_undo_retention NUMBER(5);
v_undo_guarantee BOOLEAN;
v_instance_number NUMBER;
v_undo_advisor_advice VARCHAR2(100);
v_undo_health_ret NUMBER;
v_problem VARCHAR2(1000);
v_recommendation VARCHAR2(1000);
v_rationale VARCHAR2(1000);
v_retention NUMBER;
v_utbsize NUMBER;
v_best_retention NUMBER;
v_longest_query NUMBER;
v_required_retention NUMBER;
BEGIN
select sysdate into v_cur_dt from dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('- Undo Analysis started at : ' || v_cur_dt || ' -');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
v_undo_info_ret := DBMS_UNDO_ADV.UNDO_INFO(v_undo_tbs_name, v_undo_tbs_size, v_undo_autoext, v_undo_retention, v_undo_guarantee);
select sum(bytes)/1024/1024 into v_cur_undo_mb from dba_data_files where tablespace_name = v_undo_tbs_name;
DBMS_OUTPUT.PUT_LINE('NOTE:The following analysis is based upon the database workload during the period -');
DBMS_OUTPUT.PUT_LINE('Begin Time : ' || v_analyse_start_time);
DBMS_OUTPUT.PUT_LINE('End Time : ' || v_analyse_end_time);
IF v_undo_guarantee THEN
DBMS_OUTPUT.PUT_LINE(RPAD('UNDO GUARANTEE is set to',55) || ' : TRUE');
ELSE
dbms_output.put_line(RPAD('UNDO GUARANTEE is set to',55) || ' : FALSE');
END IF;
DBMS_OUTPUT.PUT_LINE(CHR(9));
SELECT instance_number INTO v_instance_number FROM V$INSTANCE;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Undo Space Recommendation');
DBMS_OUTPUT.PUT_LINE('-------------------------');
v_undo_health_ret := dbms_undo_adv.undo_health(v_analyse_start_time, v_analyse_end_time, v_problem, v_recommendation, v_rationale, v_retention, v_utbsize);
IF v_undo_health_ret > 0 THEN
DBMS_OUTPUT.PUT_LINE('Minimum Recommendation : ' || v_recommendation);
DBMS_OUTPUT.PUT_LINE('Rationale : ' || v_rationale);
DBMS_OUTPUT.PUT_LINE('Recommended Undo Tablespace Size : ' || v_utbsize || 'M');
ELSE
DBMS_OUTPUT.PUT_LINE('Allocated undo space is sufficient for the current workload.');
END IF;
SELECT dbms_undo_adv.best_possible_retention(v_analyse_start_time, v_analyse_end_time) into v_best_retention FROM dual;
SELECT dbms_undo_adv.longest_query(v_analyse_start_time, v_analyse_end_time) into v_longest_query FROM dual;
SELECT dbms_undo_adv.required_retention(v_analyse_start_time, v_analyse_end_time) into v_required_retention FROM dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Retention Recommendation');
DBMS_OUTPUT.PUT_LINE('------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('The best possible retention with current configuration is ',60) || ' : ' || v_best_retention || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The longest running query ran for ',60) || ' : ' || v_longest_query || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The undo retention required to avoid errors is ',60) || ' : ' || v_required_retention || ' Seconds');
END;
/
Sample output:
- Undo Analysis started at : 10/10/2014 10:35:45 -
--------------------------------------------------
NOTE:The following analysis is based upon the database workload during the period -
Begin Time : 03/10/2014 10:35:45
End Time : 10/10/2014 10:35:45
Current Undo Configuration
--------------------------
Current undo tablespace : UNDOTBS1
Current undo tablespace size (datafile size now) : 3072M
Current undo tablespace size (consider autoextend) : 32768M
AUTOEXTEND for undo tablespace is : ON
Current undo retention : 2700
UNDO GUARANTEE is set to : FALSE
Undo Advisor Summary
---------------------------
Finding 1:The undo tablespace is OK.
Undo Space Recommendation
-------------------------
Allocated undo space is sufficient for the current workload.
Retention Recommendation
------------------------
The best possible retention with current configuration is : 114475 Seconds
The longest running query ran for : 5211 Seconds
The undo retention required to avoid errors is : 5211 Seconds
PL/SQL procedure successfully completed.
select a.tablespace_name,b.total_space,a.used_space, (b.total_space - a.used_space) Free_Space, round((a.used_space/b.total_space)*100,2) PCT_USED,round(((b.total_space - a.used_space)/b.total_space)*100,2) PCT_FREE from
(select tablespace_name,sum(BYTES/1024/1024/1024) used_space from dba_undo_extents where status in ('UNEXPIRED','ACTIVE') group by tablespace_name) a,
(SELECT tablespace_name, SUM (size_to_grow)/1024/1024/1024 total_space
FROM (SELECT tablespace_name, SUM (maxbytes) size_to_grow
FROM dba_data_files
WHERE tablespace_name in (select UPPER(VALUE) from gv$parameter where name='undo_tablespace') and autoextensible = 'YES'
GROUP BY tablespace_name
UNION
SELECT tablespace_name, SUM (BYTES) size_to_grow
FROM dba_data_files
WHERE tablespace_name in (select UPPER(VALUE) from gv$parameter where name='undo_tablespace') and autoextensible = 'NO'
GROUP BY tablespace_name)
GROUP BY tablespace_name) b where a.tablespace_name=b.tablespace_name
Calculate Needed UNDO Size for given Database Activity -----------------------------------------------------------------------------------------
