I 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?
I am having database A and database B and I am inserting into database b by selecting some records from database a using db link.Is there any way to find whether database link is being used?
select memberid, mi.typeid, emailaddress from memberdata@prod_db m join memberitems@prod_db mi on m.memberid = mi.memberid;
These are the tables:
SQL> desc memberdata@prod_db Name Null? Type ----------------------------------------- -------- ---------------------------- MEMBERID NUMBER EMAILADDRESS VARCHAR2(50) ADDRESS1 VARCHAR2(50)
SQL> desc memberitems Name Null? Type ----------------------------------------- -------- ---------------------------- MEMBERID NUMBER ITEMID NUMBER
Shouldn't we raise an 'ambiguous column' error on memberid?
i am creating database manually in oracle 9i os version ibm-aix5.3 it showing following error
CREATE DATABASE "varathu" * ERROR at line 1: ORA-01501: CREATE DATABASE failed ORA-00200: controlfile could not be created ORA-00202: controlfile: '/backup/varathu/control01.ctl' ORA-27040: skgfrcre: create error, unable to create file IBM AIX RISC System/6000 Error: 13: Permission denied
When I try to log into my db with a specific user I get this message. Below is from the alert log. I can login as system just fine.
----- Error Stack Dump ----- ORA-00604: error occurred at recursive SQL level 1 ORA-01438: value larger than specified precision allowed for this column ORA-06512: at line 2 Oracle 10g OEL 5.5
While after connecting to server via my userid( its an externally id) ,after setting ORACLE_HOME and SID, while entering sqlplus / command,its not connecting.Error am getting is:-
ERROR: ORA-01034: ORACLE not available ORA-27123: unable to attach to shared memory segment HP-UX Error: 22: Invalid argument
I have installed unixodbc 2.3.1, postgres odbc driver (psqlodbc-07.03) and dg4odbc 11.2..On querying : select sysdate from dual@dblink_postgresql, the following error occurs:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [unixODBC][Driver Manager]Can't open lib '/oracle/psqlodbc/lib/libpsqlodbc.a' : file not found {01000} ORA-02063: preceding 2 lines from DBLINK_POSTGRES
Similar error appears in the trace file as well.
[unixODBC][Driver Manager]Can't open lib '/oracle/psqlodbc/lib/libpsqlodbc.a' $ isql -v postgresql postgres postgres@2012 [01000][unixODBC][Driver Manager]Can't open lib '/oracle/psqlodbc/lib/libpsqlodbc.a' : file not found [ISQL]ERROR: Could not SQLConnect
However the file is present in the location and has no permission related problems.
$ pwd /oracle/psqlodbc/lib $ ls -lrt total 2952 -rwxr-xr-x 1 oracle oinstall 663 Mar 25 15:28 psqlodbc.la -rw-r--r-- 1 oracle oinstall 731419 Mar 25 15:28 libpsqlodbc.a -rw-r--r-- 1 oracle oinstall 12215 Mar 25 22:11 win_md5.o -rw-r--r-- 1 oracle oinstall 18100 Mar 25 22:11 options.o [code]....
I was surprised when I shutdown our production dB. I got this error:
$ . oraenv ORACLE_SID = [abcd] ? SQL> shutdown ORA-01116: error in opening database file 26 ORA-01110: data file 26: '/VOL03/oradata/qxyz/data/qxyz_data01.dbf' ORA-27041: unable to open file SVR4 Error: 2: No such file or directory Additional information: 3 SQL> shutdown abort ORACLE instance shut down.
2 days ago i rename this file QXYZ_DATA01.dbf from it's original name of QXYZ01.dbf without knowing that I was in the 'abcd' instance. When I backup a controlfile I noticed that it was part of the 'abcd' data files, so I just delete it (from OS level).
Is there any risk involved in the 'abcd' instance? How do I get rid of that file 'qxyz_data01.dbf' in the 'abcd' instance.
I am getting a ORA-03114 (not connected to Oracle) error when I am creating a DB using DBCA. Here are the steps that I do:
1) Log onto the server remotely, using Exceed Query 2) Connect as the oracle user (Oracle) 3) set ORACLE_HOME, PATH 4) Run DBCA, give parameters
At around 12% and again at 19%, I get the error (see screenshot). I also tried to generate the scripts and run them remotely, and the steps run successfully upto & including the step " startup nomount pfile="/oracle/admin/<dbname>/scripts/init.ora"; ". After that, when I try to run the script specified in rmanRestoreDatafiles.sql from SQL> prompt, the error appears, and connection to ORACLE is lost.
Googling the error shows that it is a connectivilty issue, and Oracle says that it is usually caused by user scripts. But I am not running any user-created scripts as part of the DBCA process. Somehow, the instance is timing out immediately after doing the "startup nomount".
I need to create one to many user DB link in oracle 10g. Meaning I have a user A in database 1 and I want to access the objects from user B,C,D in database 2, how to create a public database link so that i can have this one to many user access?
I have login through the sysdba and created dblink and modified the tnsnames.ora for necessary changes. And the it started working correctly and I can able to get the data by selecting it.But when I login with different user that is showing the following error.
ORA-12154: TNS:could not resolve the connect identifier specified
How to resolve this,is there any privileges or permission need to give to that user.
I'm getting a "TNS disconnect" after database creation script creation in DBCA. Haven't figured out what's going on. I'm attempting to create a 2nd database on one linux 64 server. I get all the way through DBCA, but it won't go the final mile. Any thoughts? I've tried setting the port to a different port from the existing LISTENER, creating a LISTENER2 and associating it with the 2nd instance.
Import: Release 10.2.0.1.0 - Production on Wednesday, 17 March, 2010 11:07:02 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production Starting "MUBA"."SYS_IMPORT_TABLE_01": muba/******** tables=FUNCTION_NO directory=testdump NETWORK_LINK=DBLINK1 Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
In one machine I have windows xp with Oracle 11.2.0.2 database installed and I am able to connect to the database using both sql developer and sql plus.
In another machine I have windows 7. I installed Oracle client 11.2.0.1, then using Net configuration tool, I installed both listener.ora and tnsnames.ora files.
Now, I want to connect to the database which is installed on windows xp from windows 7 using the oracle client. When I tried to connect, I got the following error :
ERROR:ORA-12560: TNS:protocol adapter error
In order to rectify the above error, I was making sure that ORACLE_HOME AND ORACLE_SID were properly set in the regedit file (under HKEY_LOCAL_MACHINE > SOFTWARE > ORACLE). The ORACLE_HOME was set properly. But I found ORACLE_SID missing. So I added ORACLE_SID environment variable in the registry and re-tried connecting to the database. But I am not able to connect. What else should I do in order to connect?
I found a link to a topic concerning this issue (click here), but the question mentions that the poster was looking for a "band-aid" fix...if this method will work for a more long-term solution
Is this possible to run a direct impdp with database link for transport tablespace instead of first creating export dump and then using impdp.
I tried the below command and it shows up error.I have gone through the entire Oracle docs but could not find the exact command with network link for transport tablespace.
How can i import whole schema from other database. i have a database WHO2 WHERE I HAVE A schema called stg_cupid_rp2 i want to import into data WH0T in stg_cupid_rp2 using DBlink EXP_SAT.
Connecting Oracle to SQL Server via database link.
I am trying to connect from Oracle 10G to a SQL Server database. I have looked at the manual and i will admit that i am finding the documentation quite difficult to follow. There seems to be several options to use but none of the documentation describe how each option works.
As an example, i have been given the following information on the database i need to connect to (i.e. the SQL Server database)
- Username - Password - Database Name [lets assume the database name is data_extract]
Note: In listener.ora, i only added the last SID_DESC entry. I then went on and created the database link as shown below
create database link sqlservdb using 'sqlserver.db';
When i try to access a table i get the following error
sqlplus> select * from TESTTABLE@sqlservdb; select * from TESTTABLE@sqlservdb * ERROR at line 1: ORA-28545: error diagnosed by Net8 when connecting to an agent Unable to retrieve text of NETWORK/NCR message 65535 ORA-02063: preceding 2 lines from ORASQLSERVER
A couple of things i am not sure of:
- Where do i specify the username and password to access the sql server from the oracle db to the sql server db
- Having read around on the internet, i can see some people refering to a DSN datasource. I am told that the information that i have is all i need. Is this correct or do i need something else?
- The sid i specified in inithsodbc.ora and tnsnames.ora is actually the sql server database. Is this correct?
we have the following requirement for creating materialized view.
In database "DB1", a table "abc" is there in "user1" schema. In database "DB2", in "user2", we are accessing the "abc" through DB link "DBL1" to DB1: select * from user1.abc@DBL1; we have created a public synonym for "user1.abc@DBL1" as "sabc".
In database "DB3", we need to create the materialized view to access "abc" in DB1 through DB2. we are not allowed to access "DB1" directly from "DB3". so we create a db link as "DBL2" in "DB3" to "DB2".
when i try to create a materialized view as below: create materialized view abc_mv as select * from sabc@DBL2;
But got the error as "synonym translation invalid". When i access the SQL statement separately as below select * from sabc@DBL2, its working but in Materialized view, it shown that error. Even i tried with schema name as well like "user2.sabc@DBL2" , but that also thrown same error.
Is there any option available for this type of creating materialized view?
It's been 2 days from now that I'm bugging with this kind of error in my manual dB creation. I used DBCA first but we had a default directory settings, that's why I resorted to create the db manually. The dB was already created and currently has a status of STARTED.. I cannot mount it due to this error:
SQL> startup nomount ORACLE instance started. Total System Global Area 247463936 bytes Fixed Size 2235856 bytes Variable Size 177840688 bytes
i am installing oracle database 8.1.7 on dell server power edge 2650 first time database successfully installed but when i want to crate new database by Database Configuration Assistant it is not working for new database creation.
