Which command is the right way to open a alert log file of below size.
-rw-r----- 1 oracle asmadmin 28070129 Dec 17 08:06 alert_DEMO.log
I used to open a alert log file using vi editor. Is that a right way to open and view a alert log ?
I have had the following problem open with Oracle support since March 2011 (8 months), and still no resolution.
When I export all our schema's on Sunday night it takes about 1 hour 50 minutes. When I export the same schema's on any other night it takes 7 hours. The only difference is that on Sunday at 4:00am we drop all connections in the connection pools and reestablish new connections. Then 19hours later on Sunday at 23:00 we perform the exports which only take 2 hours to complete.
I have also tried recreating the connections in the connection pools during the week, and the exports have then only taken 2 hours to complete. But the following night after the connections have been used during the day, the exports again take 7 hours. So it appears the export speed gets significantly slower when there are many open connections that have been used and not closed.
From the stats pack report I found 2 SQL statements internal to the export command, that had an order of magnitude in difference when looking at the elapsed execution time between the fast export, and the slow export (see below).
How to speed up the exports without having to drop and recreate the database connections in the connection pools each night.
FAST:
elapsed_time: 430.90
executions: 161,388
Module: exp@Oracle1 (TNS V1-V3)
SELECT COLNAME, COLNO, PROPERTY, NOLOG FROM SYS.EXU10CCL
WHERE CNO = :1 ORDER BY COLNO
elapsed_time: 264.29
executions: 50,349
Module: exp@Oracle1 (TNS V1-V3)
SELECT TOWNER, TNAME, NAME, LENGTH, PRECISION, SCALE, TYPE, ISNULL, CONNAME, COLID, INTCOLID, SEGCOLID, COMMENT$, DEFAULT$, DFLTLEN, ENABLED, DEFER, FLAGS, COLPROP, ADTNAME, ADTOWNER, CHARSETID, CHARSETFORM, FSPRECISION, LFPRECISION, CHARLEN, TFLAGS, 100 FROM SYS.EXU8COL
WHERE TOBJID = :1 ORDER BY INTCOLID
SLOW:
elapse_time: 8264.16
executions: 124,662
Module: exp@Oracle1 (TNS V1-V3)
SELECT COLNAME, COLNO, PROPERTY, NOLOG FROM SYS.EXU10CCL
WHERE CNO = :1 ORDER BY COLNO
elapsed_time: 3877.78
executions: 38,813
Module: exp@Oracle1 (TNS V1-V3)
SELECT TOWNER, TNAME, NAME, LENGTH, PRECISION, SCALE, TYPE, ISNULL, CONNAME, COLID, INTCOLID, SEGCOLID, COMMENT$, DEFAULT$, DFLTLEN, ENABLED, DEFER, FLAGS, COLPROP, ADTNAME, ADTOWNER, CHARSETID, CHARSETFORM, FSPRECISION, LFPRECISION, CHARLEN, TFLAGS, 100 FROM SYS.EXU8COL
WHERE TOBJID = :1 ORDER BY INTCOLID
I use the following export command for each schema:
$ORACLE_HOME/bin/exp user/pass file=somefile.dmp owner=$SCHEMA log=somelog.log buffer=9000000
I have an Oracle Standard edition 11.1.0.7 database on 64bit Linux with a 7GB SGA. I currently export (I use exp not datapump because datapump is a lot slower and we can't use parallel processing features of datapump on a standard edition database) approx 200 schema's each night. The export normally takes 1 hour 50 minutes which is approximately 2 schema's exported every minute. When the exports run slowly each export takes almost 2 minutes to complete.
The database has about 20 GB data and 50 GB indexes. The database has also approx 500 connections via toplink connection pools from 8 application servers.
one can only see the alert log, trc ...with sys log in?
View 12 Replies View RelatedIs there any separate alert.log for ASM instances?
SQL> show parameter BACKGROUND_DUMP_DEST
The above will give the dest of the alert.log for DB only?Is is the same for ASM instances? If different then where to look for.
Our dev server has 7 instances on it (in one box). I noticed that most of it's alert logs are not up-to-date (generation is delayed by almost 1 week). What should I supposed to do with it?
View 6 Replies View RelatedI'm trying to set up alert notifications. I have to program it myself, because SE isn't allowed to do it with OEM. As far as I can see from the documentation, this should be all that is necessary:
conn / as sysdba
exec dbms_aqelm.set_mailhost(mailhost=>'127.0.0.1')
exec dbms_aqelm.set_mailport(mailport=>25)
exec dbms_aqelm.set_sendfrom(sendfrom=>'alert@noreply')
[Code]......
but this gives me:
ORA-24940: invalid combination of ANONYMOUS namespace, default presentation and
e-mail receive protocol
ORA-06512: at "SYS.DBMS_AQ", line 737
ORA-06512: at line 11
I must be missing something! Probably something very basic. There is nothing in any trace files.
where i can find an alert log errors so whenever they occur in my RAC so i can comprehend them very well. I am trying to know that what can be the errors when u have RAC with ASM and SAN .
View 3 Replies View RelatedI want to mail any "ora" errors registered into alert.log file. is it possible if yes then how can i do it.
View 22 Replies View RelatedTablespace usage alerts are checked every ten minutes. You can request a check every minute when you set the threshold, and confirm that this has been set:
orclz> execute DBMS_SERVER_ALERT.SET_THRESHOLD(-
> metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,-
> warning_operator => DBMS_SERVER_ALERT.OPERATOR_GT,-
> warning_value => '50',-
> critical_operator => DBMS_SERVER_ALERT.OPERATOR_GT,-
> critical_value => '75',-
[code]....
orclz>but you will still have to wait up to ten minutes for the alert to be raised. know whether this frequency can be changed? And why this particular alert behaves differently from all the others? This is the bahaviour in all releases since server alerts arrived.
I have created custom table in inventory and after register table & columns, i create event alert on it. event alert is not firing.
View 1 Replies View RelatedI am using oracle 10gXE just for basic practice but its showing following Errors
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 8 11:46:34 2
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL>
SQL>
SQL>
SQL> startup;
ORA-01081: cannot start already-running ORACLE - shut it down fir
SQL>
SQL>
SQL>
SQL> select * from dept;
select * from dept
[code]....
How to Close All open cursors in other session.
NAME TYPE VALUE
------------------------------------ ----------- ---------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 500
session_cached_cursors integer 50
Ex. as above we have 500 max cursor
seesion show 499 and now we need to flust all open cursors
I am facing a strange issue on 11gR2 (OEL 5.4) standby readonly with apply database.It's throwing 16000: database open for read-only access during SELECT's .
Here is snapshot of errors.
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
SQL> l
1* SELECT t0.airportID, t0.archived, t0.assetTag, t0.bluetoothID, t0.cmBundle, t0.createdDate,
t0.currentProductTaskID, t0.ethernetID, t0.failOrReworkCount, t0.highestCompletedTaskTypeID, t0.lastModDate, t0.lastStationID,
t0.modCount, t0.modelID, t0.oemSerialNumber, t0.orgSerialNumber, t0.pdmVersion, t0.preburnComplete,
t0.productID, t0.reworked, t0.secondaryEthernetID,
t0.serialNumber, t0.shipped, t0.specialBuildTypeID,
[code]....
My current database MAX OPEN CURSORS VALUE=300.
Applications "A","B","C" using same DB user (USER1) then connect to a database.
Last night I created dedicated DB user (USER2) then application "B" connect to DB using this new user.Starting traffic increase today morning "B" application receiving "ORA-01000: maximum open cursors exceeded" error from DB.When I change current DB user USER2 to USER1, application did not receiving above errors.
I don't understanding what happening in database?
I am using oracle 10g R2. Some how control file is corrupted and database is not open. and there is no backup of control file. Now i need to open the database without recreating the database.
View 5 Replies View RelatedI searched the knowledge base here and in other forums and haven't found the answer. There was a similar condition presented earlier, but it turned out the resolution was to run the >shutdown/>startup on the server rather than from a client. Not applicable in my case!
Shutdown and startup called used to work with 9i, now startup fails in 10g. ORACLE_HOME and ORACLE_SID are set and report correct values. What is different in 10g that the script (shown below) now throws a TNS listener error after shutdown?
I successfully upgraded from 9i to 10g (10.2.0.5.0) on Windows Server 2003, 32-bit last weekend. Now our backup_script.bat and oracle_stop_start.sql throw "ORA-12514: TNS:listener does not currently know of service requested in the connect descriptor"
This batch file and sql script predate me and worked with 9i for years. Now the startup command in the oracle_stop_start throws this error after executing shutdown. The backup batch file calls:
sqlplus "system/manager@wind as sysdba" @D:ptcwindchillackupsoracle_stop_start
The entire oracle_stop_start.sql is as follows:
shutdown immediate;
startup open;
quit
What is different in 10g that this now doesn't work? Shutdown works, but it will not startup due to the listener error. I have made the backups work by commenting out the call to sqlplus and I'm getting good backup dumps.
In my virtual image of the server I have found that I can duplicate the error after >shutdown. I have found that if I exit sql*plus and go back in as follows I can get the database instance to restart from the command line:
c:>sqlplus /nolog
sql>connect sys/* as sysdba
sql>startup open wind;
That does start, mount and open the database instance.
Is there a better way to be making sure that everything is stopped before executing the >exp command to create a backup dump? I will look into and test datapump export/import and RMAN for creating the backups, after I get this working again.
why this worked in sql*plus in 9i but not in 10g? Can I make TNS Listener smarter? Change or add variables to the startup command?
In my environment found maximum open cursor exceeds error. So how can I found the open cursor list and how can I close that cursor without restarting. Any SQL commands to close the open cursor.
View 4 Replies View RelatedWhen i am trying to shutdown the database it is not recognising it.It is giving error.
SQL>shutdown immediate unknown command beginning "shutdown i..." - rest of line ignored.
I am passing this command on Sql prompt.So i don't think problem in setting ORACLE_BASE,ORACLE_SID.
I use following command to determine which table can benefit from shrinking
select * from
table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'))
order by reclaimable_space desc
then i give following command to get reclaimable space
alter table t1 enable row movement ;
alter table t1 shrink space cascade;
alter table t1 disable row movement ;
in table t1 427MB was shown as reclaimable space, after executing above commands, i run dbms_space procedure again to check the out come, but result was same.I understand tablespaces are by default ASSM in 11g, none of table has LONG datatype or LOB indexes or MVIEW with ON COMMIT.
I do below alter command alter tablespace RCA_MB_IDX add datafile '/gxsdb/database oradata5/RCA/RCA_REF_MB_IDX_01.dbf ' size 1024M
But there is extra space in the file location '/gxsdb/database/oradata5 RCA/RCA_REF_MB_IDX_01.dbfX' I mark X for that space ...
The command is commit .
But there is problem when do backup ...as the file cannot be find since there is extra space after ".dbf" How can I fall back ?
I am trying to run PL/SQL Developer from command line, So it can open with an opened SQL WINDOW by default, with a script (which I specify in the command ) already in it:
I've tried:
"C:Program Files (x86)PLSQL Developerplsqldev.exe" userid=andrey/andrey@mydb
but it just opens the application with a connection, no file.
*CORRECTION* While I was writing this post, I tried something , which is not so sophisticated, and it appeared to work...
C:Program Files (x86)PLSQL Developerplsqldev.exe" userid=andrey/andrey@mydb "C:Program Files (x86)PLSQL Developerandrey.sql"
I am facing one problem we used to copy any document and for open any document in WINDOWS
Host('CMD /C COPY '||'"'||:Old_Doc||'"'||' X:�3�3'||:New_Doc);
Client_Host('Cmd /C Start '||'X:�3�3'||:New_Doc);
But these commands not running in Linux.
how to resolve the below error.. I am getting this error on the alert log .. oracle DB version is 11.2.0.1 and os is AIX6.1
ORA-1403 encountered when generating server alert SMG-4121 Thu Jun 07 13:38:27 2012
ORA-1403 encountered when generating server alert SMG-4121 Thu Jun 07 13:39:00 2012
ORA-1403 encountered when generating server alert SMG-3503 Thu Jun 07 13:43:27 2012
ORA-1403 encountered when generating server alert SMG-4121 Thu Jun 07 13:48:27 2012
I am trying to get a readable version of a .trc file generated by Oracle
10.2.0.4.0 with tkprof, but I still have been unable to get this done...and this is what I have already tried:
tkprof /u00/app/oracle/admin/DB/udump/*BITN1234.trc /batch/salidas/student/BITNTRACE.txt
And whether I run this from my PL/SQL process (PRO*C) or from a command line, it returns:
TKPROF: Release 10.2.0.4.0 - Production on Thu Jan 6 11:04:38 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
could not open trace file /u00/app/oracle/admin/DB/udump/BITN1234.trc
I have already (from my PRO*C code and from command line)...
- made sure that the file is in the directory.
- run this from the udump directory, where the .trc file is...didn't work.
- run this from the udump directory, and specifying explicitely the
complete path anyway in the tkprof line (redundant...I know)...didn't work.
- tried to copy the file to another directory in order to run the tkprof,
and it returns:
cp: BITN1234.trc: The file access permissions do not allow the specified
action.
- tried changing privileges with:
chmod a+r BITN1234.trc
and it returns:
chmod: BITN1234.trc: Operation not permitted.
- tried to run tkprof01 instead of tkprof and it returns:
We trust you have received the usual lecture from the local System Administrator. It usually boils down to these two things:
#1) Respect the privacy of others.
#2) Think before you type.
we are running SAP application against oracle database. say, if I use brspace or brtools (from SAP side) to shutdown or startup database or collect stats, does this mean it not recommend to use oracle command to shutdown/start & collect stats?
View 3 Replies View RelatedWhen I am creating service for oracle using oradim Utility ,following messsage appearing
F:Users>oradim -new -sid stby -startmode manual
DIM-00014: Cannot open the Windows NT Service Control Manager.
O/S-Error: (OS 5) Access is denied.
I am recieving errors when trying to load the control file. The errors are as follows:
SQL*Loader-500 Unable to open file (homework.ctl)
SQL*Loader-553 file not found
SQL*Loader-559 SYstem error: The system cannot find the file specified.
My control file is located directly in the C drive (C:homework.ctl). The control file contains the following
LOAD DATA
INFILE 'c:country.dat'
APPEND INTO TABLE homework
fields terminated by ',' optionally encloded by '"'
(country, month, day)
WHEN (month='April')
The command I am entering is:
sqlldr system/password control=homework.ctl
I've tried c:homework.ctl, 'c:homework.ctl', and placing the file in the BIN folder of Oracle.
I want to create a wallet on RAC setup.I have two node setup.I have created the wallet directory under shared folder /u01/oradata/$ORACLE_SID/wallet
I am Unable to open wallet.I tried this using the below command
SQL> alter system set encryption key identified by "aryabhat";
alter system set encryption key identified by "aryabhat"
*
ERROR at line 1:
ORA-28353: failed to open wallet
Following is the content of sqlnet.ora file
(path : /u01/app/11.2.0/grid/network/admin/sqlnet.ora)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ADR_BASE = /u01/app/oracle
ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE)(METHOD_DATA =
(DIRECTORY = /u01/oradata/$ORACLE_SID/wallet/)))
What is the parameters of exp and imp command?
View 4 Replies View RelatedI'm getting an error when trying to use the new Data Pump Export/Import utility.
I am able to create a directory using SQLPLus, and I get the "Directory Created" message, but no directory actually gets created on the server.
SQL> CREATE DIRECTORY datapump AS 'C:Inetpubdatafiledatapump';
Directory created. But I dont see the directory created on the server.
Then on the server:
C:Documents and SettingsAdministrator>expdp ******/****** FULL=y DIRECTORY=datapump DUMPFILE=expdata.dmp LOGFILE=expdata.log
Export: Release 10.2.0.1.0 - Production on Wednesday, 01 November, 2006 1:51:55
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation