When i am trying to shutdown the database its getting HANG, and alert log showing
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
Which force me to use Shutdown Abort.
DB version in 10.2.0.1.0
OS Linux 5.5
I found in alert log that PMON "failed to acquire latch" during shutdown.What it means and how to rectify the same.
View 2 Replies View RelatedOn windows server how can we see if the PMON or SMON is running? Is there a way, as we can see on linux using?
View 6 Replies View RelatedDetails of my site is as below:
database version: 11.2.0.1.0
OS version:
Microsoft Windows Server 2003
Standard Edition
Service Pack 2
the day before yesterday when my database bounce i m facing with the following error.
ksuapc : ORA-1033 foreground process starts before PMON
I am getting the Oracle Database error "ORA-00069 cannot acquire lock -- table locks disabled for string" in a stored proc. According to some sites I looked at, it says the Cause is "A command was issued that tried to lock the table indicated in the message. Examples of commands that can lock tables are: LOCK TABLE, ALTER TABLE ... ADD (...), and so on.". And the Action they suggest is "Use the ALTER TABLE ... ENABLE TABLE LOCK command, and retry the command".
I am not doing either a lock table or alter table command in my procedure, so what else could be causing this?
I want to simulate latch : cache buffer chains wait event due to use of nested loop join for lookup tables
This is what a tried :
-- create parent / child tables
SQL>drop table emp1 purge;
drop table dept1 purge;
create table dept1 (dept_id number primary key,
dept_name char(30));
[Code]....
I traced many queries like the one given below (dept_id between 1 and n where n varied from 10 to 1000) but they always result in hash join
1* select d.dept_name, e.id from sys.dept1 d, sys.emp1 e where d.dept_id = e.dept_id and e.dept_id < 1000
Execution Plan
----------------------------------------------------------
Plan hash value: 619452140
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 998K| 41M| 680 (2)| 00:00:09 |
|* 1 | HASH JOIN | | 998K| 41M| 680 (2)| 00:00:09 |
|* 2 | TABLE ACCESS FULL| DEPT1 | 999 | 34965 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP1 | 999K| 8780K| 672 (2)| 00:00:09 |
----------------------------------------------------------------------------
what can I do to get a nested loop join to simulate latch : cache buffer chains?
i want to drop a table by applying fol
SQL> drop table table1 purge;
and i get ORA-00054 resource busy and acquire with NOWAIT specified or timed expired
This table is of 600 Gb in size and no more used and not involved in any logical and physical backup and do not referenced by application but still i am getting this error.i search for this error and found which is to "Wait and retry" but i waited and then apply but still no progress.
Following query is hanging either with 'Sequential access read' or 'Latch Free' wait event Important thing is the table which is self joined in subquery here does not have any index at all While it was hanged I tried to get trace of it and terminated twice. As such haven't got 'row source generataion' The table has only 120000 records and it shall update 34000 records
UPDATE invoice_header inv
SET inv.modified_due_date =
(SELECT inv1.btn_due_date
FROM invoice_header inv1
WHERE inv.dct_code = inv1.dct_code AND inv1.release = 'A5')
[code]...
During 'sequential read' using p1,p2 values tried to get what the session is reading and found that it is using the table itself.
During lath free I found following
SELECT name, 'Child '||child#, gets, misses, sleeps
FROM v$latch_children
WHERE addr= (select p1raw from v$session_wait where sid=18)
UNION
[code]...
However instead of self join when I creaed global temporary table as
create global temporary table t as select * from invoice_header where release='A5'
And used it in the update as
UPDATE invoice_header inv
SET inv.modified_due_date =
(SELECT t.btn_due_date
FROM t
WHERE inv.dct_code = t.dct_code AND t.release = 'A5')
WHERE inv.release = 'A5' AND inv.btn_due_date >= TRUNC (SYSDATE)
It updated the records in a second!!
Questions are
1) why it is producing 'sequential read' wait event when there is no index access or else why it is doing single block access when FTS is required?
2) Why is the 'latch free' wait event here and what it indicates here with 'cache buffer handles'?
Is it because we are reading and updating the same segment?
know in case DDL of table is required. It has all nullable columns and no index at all. Since it is 9i I am unable to use MERGE effectively in this case
Does cache buffer chain latch and buffer busy wait event are related to one any another.
Latch definition from Google says : Latches are simple, low-level serialization mechanisms to protect shared data structures in the system global area (SGA).
what does it mean my protect. Does this mean protects from aging as per LRU algorithm and getting removed from SGA
or
protect from other processes ,say from example from simultaneously DML operations.
or
both
Does buffer busy wait event occurs , because of the cache buffer chain latch ?
I am trying to import database dump using the following command
CODEimpdp system/xxxx@xxxx schemas=staging
remap_schema=staging:staging directory=DUMPDIR dumpfile=staging.dmp logfile=impdpstaing.log
TRANSFORM=SEGMENT_ATTRIBUTES:n
its importing data fine upto some stage after that oracle gives the following error
CODEProcessing object type SCHEMA_EXPORT/JAVA_SOURCE/JAVA_SOURCE
ORA-39097: Data Pump job encountered unexpected error -1423
ORA-39065: unexpected master process exception in DISPATCH
ORA-01423: error encountered while checking for extra rows in exact fetch
ORA-04030: out of process memory when trying to allocate 123404 bytes (QERHJ has
h-joi,kllcqas:kllsltba)
ORA-39014: One or more workers have prematurely exited.Job "SYSTEM"."SYS_IMPORT_SCHEMA_04" stopped due to fatal error at 11:42:03
I though its due to lack of memory, so i have increased pga_aggregate_target=512MB to 600MB still i am getting a same error. System configuration:
Microsoft windows Server 2003 R2,
standard Edition
Service Pack 2
Intel® Xeon CPU 3.00GHz,
4 GB RAM
SWAP MEM: 4106 MB
how can I do an archivelog dump?
View 3 Replies View RelatedI want to know whether it is possible to use export dump from Oracle 11.1.0.6 WIN 2003 SP2 Server to Oracle 11.1.0.6 Windows 7 standalone Machine (Test machine).If yes how to do it as i have tried but it is returning errors.So i tried to do it by pre-creating Tablespaces but even then its failing.
Note : I am Using EXPDP and IMPDP
i had import a database dump file & this database has username/password say abc/xyz. my database username/password is system/vinod. after importing i'm unable to login with system/vinod(error:invalid username/password) and one strange thing was happened;a user was created with abc/xyz(username/password). after altering my user only then i'm able to login with my original user.
View 1 Replies View Relatedwe have a requirement to export full database dump from source database to target database.
source database - Oracle 11.2.0.1
OS version - AIX 5.3
character set: UTF8
target database - Oracle 11.2.0.3
OS version - 6.1
character set: AL32UTF8
i did export from source database and give it to DBA of target database as dump files.
when he tried to import using this dump, he got the following error for 3 tables
ORA-02374: conversion error loading table
ORA-12899: value too large for column
ORA-02372: data for row
DBA is telling there is character conversion issue and i need to change the source database character set (NLS_CHARACTERSET) and then export these 3 tables separately.
But on analysis, i found UTF8 is subset of AL32UTF8 and hence oracle would do this conversion implicitly.
My query is:
1. For this issue, only solution is to change the source database character set as same as target database, then do the export or any other way available?
2. If i need to change the source database character set, would it affect other data available there?
3. Is there any way available doing character set conversion while doing "expdp" on the fly?
4. Is this issue comes because of oracle version (11.2.0.1 to 11.2.0.3 ) or OS (AIX 5.3 to 6.1)?
how can i import the oracle 9i dump file into 10g database, while iporting i get following error imp-00002 fail to open dump file
View 4 Replies View Relatedhow can i dump and transfer it to another year with the same table
View 6 Replies View Relatedmy problem is that whenever i want to import a dump file(oracle 10g) oracle just import 4 tables and then goes into hang state(not responding) i'm using old import method (not datapump).
View 1 Replies View Relatedwhat the following output of the oracle function "dump" means?
query: select dump(:new.messaging_state) from dual
output:
Typ=2 Len=1: 193
I need this debug output because I want to know which value the program wants to write in the columns messaging_state.
The update fails because of a parent Key violation.
i got many audit file in a dump it is on hp-ux linux normally in linux i use to give
find -name "*.aud" -mtime +20 -exec rm {} ;
what to give in HP-UX linux ?
IMPDP-ing a dump file that someone has handed me over into Oracle XE results in special characters, i.e. Umlauts, being messed up.
In a hex editor, the dump file shows a) the token WE8MSWIN1252 near the beginning, but b) Umlauts obviously being encoded in DOS 850, for example "König" is encoded as 4b 94(!) 6e 69 67. Does this prove that the dump file is badly formatted and that I have to resign myself to the complicated approach mentioned at the end of [URL]...
table1-- EMPLOYEE
empno ename dept
001 david 20
002 thomas 30
I need query to dump all the values to below table as show below
table2 -- DEPT
empno info
001 david
001 20
002 thomas
002 30
We have an old full export .dmp file from a 10g db and there are 451 records in one specific table that we need to export. Is it possible to IMP just the one specific table from a full dump? Or, another option, can we extract the records from the one table in the .dmp file into an xml file?
View 14 Replies View RelatedI have a db instance in which several schemas are there. I have taken a dump of a particular schema(user) using oracle exp command
exp <username>/<passwd>@<dbname> FULL=y FILE=export.dmp LOG=exportdb.log CONSISTENT=y
Now I have another db where I wish to import the above dump. This is not a empty db, however, I have dropped the particular user from this db for which I have created a dump above. Then I have created the same user using 'create user....'.And now I am trying to import the above dump into this.
imp '<username>/<passwd>@<dbname>' FILE=C:\DUMP\Dump\export.dmp LOG=C:\DUMP\logs\imp_dmp.log FULL=Y
import goes fine but I gets the circular synonym error for some of the types.
IMP-00003: ORACLE error 4055 encountered
ORA-04055: Aborted: "O_BULK_TARGET_SELECTOR" formed a non-REF mutually-dependent cycle with "T_GENERAL_IDLIST".
ORA-06550: line 5, column 25:
PLS-00421: circular synonym 'PUBLIC.T_GENERAL_IDLIST'
The db from which I created the dmp has no errors for these objects, but while impoting it gives these errors.I have even tried taking the whole database dmp(not a particular user), and importing it into full empty database. Then also I gets the same error.
How to read the treedump contents of a index IDX_TB_TEST_N1?
SQL> select object_id,object_name from dba_objects where owner='HXL';
OBJECT_ID OBJECT_NAME
---------- ---------------------------------------------
51786 IDX_TB_TEST_N1
alter session set events 'immediate trace name treedump level 51786'
/u01/app/oracle/admin/oracl/udump/oracl_ora_2679.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
[code]...
Is there is any way of reading from oracle dump file?
View 16 Replies View Relatedi need to dump the table from A schema to B schema with different table name.
Suppose i have TABLE A IN "A" SCHEMA i need to dump the table with DATA+sTRUCTURE in " B"SCHEMA WITH TABLE NAME AS B.
When exporting a 11g R2 database with expdp using full=y the logfile does not show that package bodies have been exported
(DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC is processed but no DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY)
When importing the dump into an empty database only package specs (besides the other objects) have been imported but all package bodies are missing.
Is it possible to determine whether the dump file is created using data pump export or normal export method by just looking at dump file, If yes, how ?
Why i am asking such question is...normal export and data pump export would create a dump file with an same extension filename.dmp. So to avoid confusion during import, i would want to determine by what method the dump file was created.
Also this would be useful for me at the scenario when the customer sends me only the dumpfile and ask to import into target database. ( may be the customer don't know in what method the dump file was created ).
I have exported a schema dump with schema name as 'A'.I want to import that dump in to schema 'B'.how ?
View 5 Replies View RelatedI have A Daily hot backup using Expdp Command On oracle 10g R2 installed on the Linux server. And I'm trying to move this Dump File to Another directory on Windows server 2003 over network using Ftp script which will be run after the export process finished Automatically.
View 9 Replies View Related