I would like to try to rename a newly created dB. Referring to the topic posted on your website[URL]... had some clarification before executing the dbrename.sql mentioned there:
After modifying the the init.ora and tnsnames.ora, do I have to rename all OLD, existing directory path (for controlfiles, datafiles, etc.) with the new-- meaning I don't have to create another directory for it's 'NEW' name?
While renaming the datafile.
1) ALTER TABLESPACE .... OFFLINE;
2) CHANGING THE DATAFILE NAME IN OS LEVEL;
3) ALTER TABLESPACE .... RENAME DATAFILE 'FILE_OLD' TO 'FILE_NEW';
4) ALTER TABLESPACE .... ONLINE;
In the above steps, I HAD FORGOTTON TO FOLLOW THE 2ND STEP.
At the step of 3rd, i got error message, now i am not able to change the name in OS level Also.
LSNRCTL> status
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date 09-NOV-2012 19:55:06
Uptime 0 days 0 hr. 24 min. 26 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:oracleproduct11.2.0dbhome_1
etworkadminlistener.ora
Listener Log File c:oraclediag nslsnrTESTQ51listeneralertlog.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=NDCORCKSDQ51.global.loc)(PORT=1521)))
Services Summary...
Service "testdb" has 1 instance(s).
Instance "ksdtest", status READY, has 1 handler(s) for this service...
Service "testdbXDB" has 1 instance(s).
Instance "testdb", status READY, has 1 handler(s) for this service...
The command completed successfully
i am trying to rename the logfile
"c:oraclediag nslsnrTESTQ51listeneralertlog.xml" to "c:oraclediag nslsnrTESTQ51listeneralert estdb.log"
so far i tried
LSNRCTL> set log_file testdb.log
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-01251: Cannot set trace/log directory under ADR
LSNRCTL>
Provide automated shell script for renaming oracle DB 10gR2. Now it is a daily manual job for us.
View 4 Replies View Relatedi have a problem in renaming the column of a table and it throws a strange error-"ORA-14155: missing PARTITION or SUBPARTITION keyword " ,but i have used correct syntax which is that " alter table temp rename id to num; " where is the problem.
View 2 Replies View RelatedI have another question to ask on packages..
I have a dictionary under the schema
OWNERSYS
DIRECTORY_NAMEUTL
DIRECTORY_PATHc:oracleoradataspmap1utl
I have cretaed an external table to read data to a table from a csv file placed in "c:oracleoradataspmap1utl". The csv file name is say "pildata.csv"
I have cretaed a package to read data from the exteranl table and to insert it into a table.
INSERT INTO M_PILEINT SELECT
A.AREA AS "AREA",
A.SUB_FAC_DESC AS "SUB_FAC_DESC",
A.SCOPE_DETAIL AS "SCOPE_DETAIL",
A.MTO_ISSUE_DATE AS "MTO_ISSUE_DATE",
A.MTO_TAKE_BY AS "MTO_TAKE_BY",
A.COMMODITY_CODE AS "SECTION"
A.PILE_NAME AS "PILE_NAME"
FROM M_EXE_PILE A
(where M_EXE_PILE A is the external table which is reading from pildata.csv)
The package runs good and data is populated to M_PILEINT .Is there a way , I can rename the csv file (say to pildata_logxxxx.csv.. something like that) from within the package.Whenever the package is run , it will copy the data from exteranl table and renames the csv file to something else..?
I've 2 schemas namely department, hrNow department schema is having one table EMP . And a view is created on that table with the name R_EMPSchema Name :
departmentTable Name : EMPView Name : R_EMPGRANT SELECT ON R_EMP TO HR ; In HR schema a synonym has been created on view R_EMP with the name R_EMP .CREATE OR REPLACE SYNONYM hr.R_EMP for department.R_EMPSchema Name : hr Synonym Name: R_EMP
Now I want to rename the view name R_EMP to V_EMP from HR schema only So, I tried the folloiwng syntax in HR schema (All the operations should be done from this schema only )[code] RENAME department.R_EMP to department.V_EMP ;
Error: ORA-01765: specifying table's owner name is not allowed[/code] How to change the syntax to make the statement work from HR schema .
I want to be able to name a column created from my query.
Query is:
select A.OrigRef, A.DisplayName, A.ExtCode, count(CalcId)
from OrigRefView A, CalcView B where A.OrigRef = B.NewRef and A.OrigRef like 'AB%'
group by A.OrigRef, A.DisplayName, A.ExtCode
order by A.SusRef
and it returns the Count in "column4" Is there a way I can get the query to output a different column name without creating a whole new table (i.e. not by creating a new table for my query output and then running a new procedure at the end to rename the column...)?
i.e. I want "column4" to read "CountofCalId"
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
Is there any way to rename a column in a table A, so that it is changed wherever it is used, like all the procedures that uses this column, or other tables that has reference to this table A?
How can we Rename or Relocate the SYSTEM datafile?
View 3 Replies View RelatedWe are planning to change the dbname from appr to apdr. Its a DR (Standby Database).
a. NID option will work for this? - DB in mount state (Standby db)
b. Is that possible to change the Instance name also?
c. Any Prerequsities and postrequirments for this change? (ie,Data Broker, communication with Primary and Standby etc?
Oracle 10g, OS - Sun Solaris
When a user is renamed in Active Directory, they can no longer connect to the Oracle DB thru OS authentication. There is no OID/DIP integration.
sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)
NAMES.DEFAULT_DOMAIN = cal.com.br
create user "CALRENATOH" IDENTIFIED EXTERNALLY
GRANT CREATE SESSION TO "CALRENATOH"
AD User CALRENATOH can connect to DB as 'sqlplus /'
But after renaming AD User CALRENATOH to CALRENATOH1 and dropping DB user CALRENATOH and creating DB user CALRENATOH1 drop user "CALRENATOH"; create user "CALRENATOH1" IDENTIFIED EXTERNALLY;
Now OS authentication 'sqlplus /' fails 'ORA-01017: invalid username/password; logon denied'..Once I recreate the DB user with old AD user name 'CALRENATOH', OS authentication succeeds. create user "CALRENATOH" IDENTIFIED EXTERNALLY;
C:Windowssystem32>set username
USERNAME=RENATOH1
C:Windowssystem32>sqlplus /@rmlab001
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Jul 3 15:16:46 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning and OLAP options
Why the Database is still looking for old AD user name? Does Oracle cache information about OS authenticated users?
i'm a student currently learning database administration security.
I need to create a tablespace for administration of database but i don't know what datafile settings are best suited for admin usage.
I have attached the schema that was given to me for this assignment.
My manager is asking to create a SQL scrip, which will provide following information.
Our database is 9.2.0.7.0 on Windows 2000 Server
-How many records Insert / Update or Delete daily in my Oracle database?
- Check Archive log / Redo Log switching information?
-Check database growth ?
i am trying to install Oracle 10.10.2.0 on Windows Server 2003 standard x64 Edition Service Pack, but when i try to run the installer or open DVD it gives me below error.
"The image file D: is Valid, but is for a machine type other than the current machine."
I was trying to delete the database in the test server. When i was deleting listener was already stopped, i continued deleting using dbca, it shown me some alert that datafiles cant be deleted because system could't find database, since listner was stopped so only service was deleted(the one showing in the windows administrator toolsservicesOracleServiceTEST).
All the datafile parameter files are still there. How can i delete the datafiles and parameter files belongs to that database or how to create the deleted service, so that i will start the listener and do the complete deleting of the database.
I have to migrate production database version 10gR2 on windows 2003 server to oracle 11gR2 on windows 2008 server . So far i have just installed the oracle software on 64bit windows 2008 server. How to move the data or migrate from old server to new server .
View 22 Replies View Relatedafter a long time,(i never remember when was the last time i installed 9i)i was asked to install 9i.So, whether i install server or cilent i get this error <attachment>
after i ignore it and finished the installation,The server installation gets abruptly ended for the client i get TNS errors.
I am trying to find the unix process for one of my application in the database but I am unable to view the same. To simulate, I did the following.
1. My database runs on different server.
2. I invoked "sqlplus" from another unix box to login to the database.
3. I found that the process id (ps -ef |grep sqlplus).
4. When I execute the below mentioned query it does not display the process id that I am looking for. But the osuser, username, program and machine details are correct. How can I know the process details from the database?
SELECT SYS.GV_$SESSION.OSUSER, SYS.GV_$SESSION.USERNAME, SYS.GV_$PROCESS.SPID,
SYS.GV_$SESSION.MACHINE, SYS.GV_$SESSION.PROGRAM,
SYS.GV_$PROCESS.PROGRAM ,SYS.GV_$SESSION.SQL_ID
FROM
SYS.GV_$PROCESS, SYS.GV_$SESSION
WHERE
SYS.GV_$PROCESS.ADDR=SYS.GV_$SESSION.PADDR and SYS.GV_$SESSION.USERNAME='TEST'
and SYS.GV_$SESSION.MACHINE like '%hostname%'
While creating the index we are getting the error "ORA-00603: ORACLE server session terminated by fatal error".
We have the space in tablespaces and also in the file systesm.
I have database in single server. I already have ORACLE_SID=stagedb. So i want to create new sid and named as proddb.Is it possible have multiple db in single server? This server not running as RAC. It run as single only.
View 1 Replies View Relateddatabase administration , we are planning to use amazon cloud database , this database does not allow us to login to server machine , unfortunately amazon don't provide ssh to this machine , in general for doing any of adminstration task on the database will there be need to log into the machine ? we can always log in through toad or any other sql client but we cannot do ssh to server..general can this limitation effect administration ?
View 9 Replies View RelatedI want to install Oracle 11g R2 in windows 2008 64 bit server. How can I know whether my server is ready to install Oracle ie is all components are available in server or any patch is to be applied etc.
View 3 Replies View RelatedI'm trying to connect a oracle client application on the client machine to a remote oracle server on the server machine but i get a connection fail.
On the server machine I configured oracle server in the following way:
Installed oracle server. Created a database "DB_Test" with the database configuration assistant Created a LISTNER with the Oracle NET Manager with the following parameter:
Protocol: TCP/IP HOST: server pc hostname (ENZOVAIO) or server machine address ip (192.168.0.71) in the network lan Port Number: 1521 Created "dbtest" service with the Oracle NET Manager with with the following parameter:
Service Name: "dbtest" Protocol: TCP/IP HOST: server pc hostname (ENZOVAIO) or server machine address ip (192.168.0.71) in the network lan Port Number: 1521
All services on the server machine are running and I opened port number (1521) in the router. On the client machine I installed SQL PLUS and SQL Developer.
With SQL Plus as by the official documentation I have entered the following command:
CONNECT username/password@[//]host[:port][/service_name]. In my case is:
CONNECT SYSTEM/oracledb@//ENZOVAIO:1521/testdb.
With SQL Developer I have entered the same parameter.
But with both SQLPlus and SQL Developer the connection fails.
We a running oracle9i database and forms 6i application.
We have 4 gb ram size and sized the sga for 700mb pga for 400mb for past 4 months there is no problem.
Now from last week we encounter the server hang, and we do restart. How to check reason for hanging.what are all the check list to carried out.
we have oracle server. we have to connect 500+ client machine to oracle server.
is it i need to install client version to all 500+ or any other shortcut way?
We performed image copy of production Oracle server (OS and instances) to a backup server. After a few weeks, we try to restore a latest Oracle database backup from production server to backup server. As we know, Oracle instance must be unique on the network.
Even we log on to backup server and bring up the instance, I think that still point to production instance since all init file, TNSNAMES.ora and listener file are still same. If we restore the database, we will end up bring down the production instance and restore on top of productions. How to change instance name on backup server including TNSNAMES, sqlnet, listener files in order for us to restore Oracle database from production to backup server?
I recently installed Oracle 10g on my windows Xp laptop. It has become considerably slow since then. I want to start the database server only when I need it, and not every time I start my laptop. I looked around in OEM and did found a way.
View 5 Replies View RelatedI have installed oracle 11g Release 11.1.0.1 on windows server 2008.I access this server from a client machine running on winXP through SQL PLUS. I wanted to shutdown and start DB server from this client machine. Accordingly i issued shutdown immediate command from SQLPLUS.
Now when i tried to start the DB with "STARTUP NOMOUNT" command i received error 12514. I understand this error means that the listener received a request to establish connections to a database but since DB services are not up and running, hence the error. So my first question is how do I resolve this error.
Continuing further, i tried to re-connect to the database as sys, and i received another error ORA-01041. So, my second question is why did i receive this error and again how can i resolve it
may be you can point me to relevant reference in Oracle documentation) how can i start and stop oracle from a client machine using SQL interface in CLI mode and windows environment and avoid this error.
I am connected as System. It was the only user I set-up a password when installed the database on personal computer.
SQL> alter user sys identified by mypass007
2 /
User altered.
SQL> connect sys/mypass007
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
[code]...
SQL> conn sysoper/mypass007
ERROR:
ORA-01017: invalid username/password; logon denied