db :oracle 9.2.0.5
os :solaris
how to find the root causes for temporary table space to grow unexpetedly and how to claim that grown space back automatically after the transaction over.
While I was exporting ( schema level ) ,I am getting following errors :
vaughn$ exp parfile=qar808par.txt
Export: Release 11.1.0.7.0 - Production on Tue May 10 10:52:34 2011
Copyright © 1982, 2007, Oracle. All rights reserved.
Username: system
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
Export done in WE8ISO8859P9 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified users .
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TOXFIRE_TO_TSFDA
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TOXFIRE_TO_TSFDA
About to export TOXFIRE_TO_TSFDA's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 25153 encountered
ORA-25153: Temporary Tablespace is Empty
EXP-00000: Export terminated unsuccessfully
'Parfile' contents :
owner=TOXFIRE_TO_TSFDA
file=exp_TOXFIRE_TO_TSFDA_QAR808.dmp
consistent=y
direct=y
recordlength=65535
log=exp_TOXFIRE_TO_TSFDA_QAR808.log
What is the reason, and It is saying that temporary tablespace is empty ( temp tablespace have almost free space ) why it is saying like that? And users default temp tablespace is that tablespace only ( database have only on temp tablespace
Today I found a Temporary tablespace is corrupted, generally speaking, how we can recover a temporary tablespace?
View 3 Replies View RelatedHow can we find the temporary tablespace and default tablespace names.
View 4 Replies View RelatedWe have EBS database with version 11.1.0.7 . Our tablespace is not releasing space ,as the below query show that TEM2 is using only 3 g .
SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_freeFROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) DWHERE A.tablespace_name = D.nameGROUP by A.tablespace_name,
[Code]....
I have a requirement to get create a separate temporary tablespace for application user, the tablespace name is OPC_TEMP on Oracle 11g. During the application installation we are expecting this tablespace to show up under the user_tablespaces but it doesn't. When I tried to check with the DBA, they mentioned that it is possible for this tablespace to be listed under the user_tablespaces because to have this, the quota has to be assigned to the tablespace and it is not possible to assign quota to temporary tablespace on oracle 10.2 and above due to certain limitation (oracle metalink ID 331657.1).
View 10 Replies View Relatedi found a temporary tablespace was corrupted,what happens to the database level, how to recover that tablespace? If need any recovery?
View 3 Replies View RelatedI just want to know whether we have to pre-allocate space for temporary tablespaces?
View 1 Replies View RelatedWhen we are using pga_aggregate_target and sort area is automatically managed by Oracle. Why oracle is not allowing for auto allocating extents in TEMP and still extents of uniform size of 1 MB is used.
View 9 Replies View RelatedHow we can create dictionary managed temporary tablespace in Oracle 10g.
SQL> create temporary tablespace temp
2 tempfile '+GWDAAS04_TEMP_DG01/pimsb_gw/tempfile/temp01.dbf' size 500m
3 extent management dictionary;
create temporary tablespace temp
ERROR at line 1:
ORA-25139: invalid option for CREATE TEMPORARY TABLESPACE
I am oracle DBA (trainee) new in this field.
Some time my temporary tablespace size suddenly increase from 1gb to 5-6gb why this happened ? I want only reason why it suddenly increase?
We are facing problem as temporary tablespace getting full continuously. During below running query, temporary tablespace getting full continuously and now it is not managable so we had stop the processing but we need to resolve this issue as business impact is there.
MERGE INTO HDFCMPR.MPR_TB_MPRMASTER
USING (SELECT /*+ USE_HASH(A,B) FULL(A) FULL(B) */
MER_TRACKID, TRANID, DECODE (UCAF, 'n', NULL, UCAF) UCAF,
A.ID
[code]....
Oracle 10.2.0.4 (user or developer getting error for not able to allocate ora 04031.
I am trying to do alter system shared_pool_size = 250M but it says
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool
I checked the show parameter sga_max_size = 376 M
the set upin the init.ora for pools:
###########################################
# Pools
###########################################
java_pool_size=157286400
large_pool_size=33554432
shared_pool_size=157286400
From show parameter sga;
[code]...
no rows selected
OS Linux. this was the message in alert.log;
ORA-04031: unable to allocate 88 bytes of shared memory ("shared pool","select /*+ rule */ bucket_cn...","sql area","opn: qkexrInitOpn")
Why do export-import require temporary tablespace? Since export-import do behave like DMLs, when does temporary tablespace be needed by datapump utility?
View 2 Replies View RelatedI am struggling with the ORA_ROWSCN pseudocolumn. (Oracle version is 11g). I am aware that without ROWDEPENDANCIES set on the tables, the SCN of a row is in reality that of the block in which the row is resides. The problem I am seeing goes something like this:
1) A datapump import creates a large table (400M+ rows) at 11 am in the morning
2) No subsequent changes are made to this table (at least that I know of)
3) Despite no changes being made the ORA_ROWSCN of almost all the rows changes every few minutes. (a few rows remain with SCN's indicating the original time of import, but the rest change)
I detect this behavior by running this query:
SELECT ora_rowscn, SCN_TO_TIMESTAMP(ora_rowscn),COUNT(*)
FROM test_table GROUP BY ora_rowscn
This ran around 5.15 am on 10/27 and resulted in:
72353518 27-OCT-12 05.17.21.000000000 AM 492009391
67515248 26-OCT-2 11.01.45.000000000 AM 370
Note that almost all rows have a very recent SCN. Only a tiny portion of the rows (370) have an SCN consistent with when the import completed. If I rerun the query a bit later, the SCN of the large rowcount will have advanced again.Block size on the database is 8192.
The RMAN backup is failing daily due the below issue..
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on t1 channel at 07/08/2012 02:10:38
RMAN-10038: database session for channel t1 terminated unexpectedly
database version : 10.2.0.4.0
RMAN Script :
run {
allocate channel t1 type 'sbt_tape'
parms 'ENV=(TDPO_OPTFILE=/u001/app/TDP/p0137x/tdpo.opt)'
format '%d_inc0_%U.rbck';
backup incremental level 0 database tag OPEN_INC0_DAY;
release channel t1;
}
ORA-12805: parallel query server died unexpectedly
View 4 Replies View RelatedI'm having some trouble with iSQL*Plus.I instaled oracle database and at first it worked fine, but then I installed client on the same maschine and all products that come with client, and somewhere in the meantime iSQL*Plus stopped working.
Then I uninstalled client and products that go with him (Policy Manager, Oracle Wallet...) because I thought that they have "broken" iSQL*Plus, but it still doesn't work. Finally, I re-installed iSQL*Plus, but that didn't work either. Interesting thing is that when I type in command line
" c:/miljana/bin/isqlplusctl start "
it says that iSQLPlus is started, but when I go to browser there is an error:
" Firefox can't establish a connection to the server at miljana.localhost.com:5560. "
Also from Administrative tools/Services if a try to start iSQL*Plus there is an error:
" Could not start the OracleOraDb10g_home1iSQL*Plus service on Local Computer.
Error 1067: The process terminated unexpectedly. "
I know that there is a lot of topics for this, but most of solutions where for Linux, and didn't work for me.I'm using Oracle 10g Enterprise Edition on WindowsXP
Backup for database has been working fine until today.
Today, about 1/3 of the way through the backup (large database), I got this...
RMAN-12005: error during channel cleanup
RMAN-10038: database session for channel sbt_4 terminated unexpectedly
...and the backup died.
Never seen before and the research I've done online has been less than useful. This database is an SAP/oracle db with brtools 7.20 (22) and is using the new parameter in its init.sap file, called "_rman_chan_failo = yes", to address disagreement between brtools and oracle about channel drops being ok if all datafiles backed up.
Nothing in the alert log at the time of this error.
I am trying to correct NMO NOT SETUID-ROOT (UNIX-ONLY) error. On production database (9..7). Googlng this error - it says to run root.sh as root. should this be run in /usr/local/bin. which is the default location.
I don't believe that there would be any problems - but would like some confirmation -to be safe.
I'm having issues with users logging into Oracle. I installed it on Ubuntu 12.04 running Oracle 11.2 XE. If i am root, i can run sqlplus just fine and log in. But when I use a regular user account and run sqlplus, it will just stay blank. No error messages or any feedback. I echo $ORACLE_HOME and echo $ORACLE_SID and they come back exactly as they do under the root account. My path is set up just like root has it.
It almost seems like a permission issue but even when i try sqlplus /nolog it stays blank.
[URL]
We have a 2 node RAC runing on 11.2.0.2 and last night the database was totally un-responsive. when i checked the ADDM I noticed the following:
Waiting for event "cursor: pin S wait on X" in wait class "Concurrency" accounted for 98% of the database time spent in processing the SQL statement with SQL_ID "4b2epo0eaqol9".I am wondering what option do i have here? i am looking to do the following:
1) find the root cause why the database was un-responsive
2) ADDM is listing the query, what options do we have further?
I need know the impact in my oracle database 10g R2, if i change root/oracle passwords in my Oracle RAC environment, my database using ASM and the nodes is in Red Hat 4.7.
View 1 Replies View RelatedI am studying a way to block the access from the root(UNIX) or admin(WIN) users to the database.
Are there some way to block the access by the superusers?
I am installing 11gr2 11.2.0.2 grid infrastructure software. while running root.sh on node1 it got failed.
Start of resource "ora.crsd" failed
CRS-2672: Attempting to start 'ora.crsd' on 'rac1'
CRS-5017: The resource action "ora.crsd start" encountered the following error:
Start action for daemon aborted
CRS-2674: Start of 'ora.crsd' on 'rac1' failed
CRS-2679: Attempting to clean 'ora.crsd' on 'rac1'
CRS-2681: Clean of 'ora.crsd' on 'rac1' succeeded
CRS-4000: Command Start failed, or completed with errors.
Clusterware exclusive mode start of Clusterware Ready Services failed at /u01/app/11.2.0/grid/crs/install/crsconfig_lib.pm line 6475.
/u01/app/11.2.0/grid/perl/bin/perl -I/u01/app/11.2.0/grid/perl/lib -I/u01/app/11.2.0/grid/crs/install /u01/app/11.2.0/grid/crs/install/rootcrs.pl execution failed
how can I suppress that su: (to orauser) root on none is filling my message.log up.
View -1 Replies View Relatedduring installtion of Oracle RAC 11g r1 on Linux using VMware server, all the pre cluster installation was successful, and root.sh on node 1 was successful too, but whenever I run the root.sh script on the second node I get error message "Failure at final check of oracle CRS stack. 10"
View 5 Replies View Relatedi have an linux instance running oracle 11g as oracle OS user. i am able to connect the DB from clients.
i need to access this DB from OS root user. what should i do.
sh root.sh Running Oracle 11g root.sh script...The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/11.2.0/grid
Enter the full pathname of the local bin directory: [usr/local/bin]: /usr/local/bin
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying coraenv to /usr/local/bin ...
Entries will be added to the /etc/oratab file as needed by Database Configuration Assistant when a database is created Finished running generic part of root.sh script. Now product-specific root actions will be performed.
2012-12-11 17:31:03: Parsing the host name
2012-12-11 17:31:03: Checking for super user privileges
2012-12-11 17:31:03: User has super user privileges
Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params
Improper Oracle Clusterware configuration found on this host
Deconfigure the existing cluster configuration before starting
[code]....
CRS-4000: Command Start failed, or completed with errors.
I am trying to install ORACLE 11gR2 RAC on CentOS 5.5 .
when run root.sh on first node. I am gettign 2/3 failed command lines but the end went successful
=======================================================================
ohasd is starting
ADVM/ACFS is not supported on centos-release-5-5.el5.centos
...
add nodeapps -n perflabhp03 -A perflabhp03-vip/255.255.254.0/eth0 on node=perflabhp03 ... failed
...
PRCR-1001 : Resource ora.net1.network does not exist
add scan=perflab-cluster-scan ... failed,
Configure Oracle Grid Infrastructure for a Cluster ... failed
...
'UpdateNodeList' was successful.
=========================================================================
Because of this output, when see the ./crsctl stat res -t , it showing LISTENER offline for perflahp03, and I can not see any status line for "perflabhp03-vip " and "Scan-listener".
Do I need to reinstall the entire cluster setup because of VIP issue?