DBAsupport.com Forums

DBAsupport.com Forums http://www.dbasupport.com/forums This is a discussion forum for all issues involved in databases and database development. en Thu, 17 May 2012 00:33:20 GMT vBulletin 60 http://www.dbasupport.com/forums/images/misc/rss.jpg DBAsupport.com Forums http://www.dbasupport.com/forums recover database oracle 10g (archives not present after end backup) http://www.dbasupport.com/forums/showthread.php?t=62521&goto=newpost Wed, 16 May 2012 06:18:03 GMT hi all,

i am having online db backup

(alter database begin backup;)--arch seq=010
(alter database end backup;)--arch seq=015 (5 mins before end backup)

but after end backup i don't have any archives .

Now after restoring the database , how can i bring the database up.

if already answered please help me with link bit urgent.

thanks in Advance ,

dalu ]]> Oracle Database Administration dalu http://www.dbasupport.com/forums/showthread.php?t=62521 How to connect a mac http://www.dbasupport.com/forums/showthread.php?t=62520&goto=newpost Tue, 15 May 2012 03:29:19 GMT We have installed Oracle9iAS and Oracle 10 R2 database Windows users connect with no problems. How can i connect ? Mac have Lion OS I tried using safari, but screen remains blank ! no messages ! Some one can explain what can i do ? Thanks a Lot ! We have installed Oracle9iAS and Oracle 10 R2 database
Windows users connect with no problems.

How can i connect ?

Mac have Lion OS

I tried using safari, but screen remains blank ! no messages !

Some one can explain what can i do ?

Thanks a Lot ! ]]> Oracle : How To ollivier http://www.dbasupport.com/forums/showthread.php?t=62520 RAC failover test document http://www.dbasupport.com/forums/showthread.php?t=62519&goto=newpost Mon, 14 May 2012 13:26:07 GMT Hi, Can some one share a document for testing the failover of the RAC environment. Hi,

Can some one share a document for testing the failover of the RAC environment. ]]> Oracle Database Administration skdas http://www.dbasupport.com/forums/showthread.php?t=62519 Trigger not getting user name after SERVERERROR http://www.dbasupport.com/forums/showthread.php?t=62518&goto=newpost Fri, 11 May 2012 17:07:15 GMT Dear all,

I have a trigger for failed login to catch who is the user trying to login, but fails so far. Here is the trigger:

CREATE OR REPLACE TRIGGER test
after servererror on database
BEGIN
If (ORA_IS_SERVERERROR(1017)) Then
insert into test_tb values (user, ora_login_user, SYS_CONTEXT ('USERENV', 'OS_USER') , '-1017', userenv('terminal'), sysdate);
End If;
End;
/

Connect sdfadf/dafsaf
ERROR:
ORA-01017: invalid username/password; logon denied

Connect / as sysdba
select * from system.test_tb;

A_USER USERNAME S_USER MESSAGE TERMINAL A_DATE
---------- ---------------- ----------- ---------- ------------------------------ ---------
abc -1017 FRANKLINIA 11-MAY-12
abc -1017 FRANKLINIA 11-MAY-12

I am just not able to get the user id who is trying to login, in this case the user “sdfadf”. Does any anyone has any idea how to grep this user info?

Thanks,
Unna ]]> Oracle Database Administration Unna http://www.dbasupport.com/forums/showthread.php?t=62518 setting up a second listener on a windows server http://www.dbasupport.com/forums/showthread.php?t=62517&goto=newpost Fri, 11 May 2012 14:38:48 GMT I have a windows machine running 11g that has 2 nic cards. I would like to set up another listener to listen on the second nic. then set up just a hand ful of users to connect to the database using the listener on the second nic. first thing I did was edit the listener.ora file with the following entry:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.198.178)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 129.49.154.31)(PORT = 1522))
)
)

the 2nd address is for the new nic. this works when I point the clients to the 1521 and others to the 1522. However, this machine is on a mobile van and the 2nd card is not connected when the van is out. all the machines on the van are have their tnsnames file pointing to 1521 and the clients in the office are pointing to 1522. this works when the van is here. When the van is out and the 2nd nic is not connected nothing works. Obviously when the van is out the clients in the office are not trying to connect, however the clients on the van are not able to connect even though the nic using 1521 is connected. It appears when the 2nd nic is not connected the listener won't start. Could anyone help me out, in setting this up so that when the van is here I can have clients in the office connect to the 2nd nic and the clients on the van connect to the first, and when the van is out and the 2nd nic is not connected have the clients on the van be able to connect to the 1st nic? ]]> Oracle Database Administration jayjabour http://www.dbasupport.com/forums/showthread.php?t=62517 group by query help http://www.dbasupport.com/forums/showthread.php?t=62516&goto=newpost Thu, 10 May 2012 23:12:10 GMT I have three tables ‘PROJECTS’, 'PROJECTMANAGER' and ‘MANAGERS' as per attached images.

I need a query which can list PROJECTS managed by “PRODUCT” type managers grouped with PROJECT STATUS.
If STATUS=2 then show as Completed Projects or else In Progress Projects.

Resulted table should look like as shown in attached image.

The query, I need should be generic so that it can be used in any database (MySQL/Oracle/MSSQL/DB2)

Quote:

CREATE TABLE PROJECTS
(
PROJECT_ID varchar(20),
PROJECT_NAME varchar(30),
STATUS int
);

CREATE TABLE PROJECTMANAGER
(
PROJECT_ID varchar(20),
MANAGER_ID varchar(20)
);

CREATE TABLE MANAGERS
(
MANAGER_ID varchar(20),
MANAGER_NAME varchar(20),
TYPE varchar(20)
);

INSERT INTO PROJECTS (PROJECT_ID, PROJECT_NAME, STATUS) VALUES
('project_001', 'Project 001', 0),
('project_002', 'Project 002', 1),
('project_003', 'Project 003', 2),
('project_004', 'Project 004', 0),
('project_005', 'Project 005', 2),
('project_006', 'Project 006', 0),
('project_007', 'Project 007', 1);

INSERT INTO PROJECTMANAGER (PROJECT_ID , MANAGER_ID) VALUES
('project_001', 'mgr_001'),
('project_002', 'mgr_001'),
('project_001', 'mgr_002'),
('project_002', 'mgr_003'),
('project_001', 'mgr_003'),
('project_005', 'mgr_001'),
('project_004', 'mgr_002');

INSERT INTO MANAGERS (MANAGER_ID, MANAGER_NAME, TYPE) VALUES
('mgr_001', 'Manager 001', 'PRODUCT'),
('mgr_002', 'Manager 002', 'HR'),
('mgr_003', 'Manager 003', 'PRODUCT'),
('mgr_004', 'Manager 004', 'FINANCE'),
('mgr_005', 'Manager 005', 'PRODUCT');

Please help.

Attached Images

Projects.gif (32.8 KB)

]]> Oracle Development deduyay http://www.dbasupport.com/forums/showthread.php?t=62516 Group by count query http://www.dbasupport.com/forums/showthread.php?t=62515&goto=newpost Thu, 10 May 2012 14:51:28 GMT Please see attached image for sample tables and description.

I have two tables ‘VEHICLE’ and ‘VEHICLE_CLASS’ as per attached images.

I need a query to get the result rows with how many VEHICLES each VEHICLE_CLASS has grouped by status as shown in attached image.

The query, I need should be generic so that it can be used in any database (MySQL/Oracle/MSSQL/DB2)

Please help.

Quote:

CREATE TABLE VEHICLE
(
VEHICLE_ID varchar(20),
VEHICLE_CLASS_ID varchar(30),
STATUS int
);

CREATE TABLE VEHICLE_CLASS
(
VEHICLE_CLASS_ID varchar(30),
VEHICLE_CLASS_NAME varchar(30)
);

INSERT INTO VEHICLE
(VEHICLE_ID, VEHICLE_CLASS_ID, STATUS)
VALUES
('vehicle_001', 'vehicle_class_001', 0),
('vehicle_002', 'vehicle_class_002', 1),
('vehicle_003', 'vehicle_class_003', 2),
('vehicle_004', 'vehicle_class_001', 0),
('vehicle_005', 'vehicle_class_002', 2),
('vehicle_006', 'vehicle_class_001', 0),
('vehicle_007', NULL, 1);

INSERT INTO VEHICLE_CLASS
(VEHICLE_CLASS_ID, VEHICLE_CLASS_NAME)
VALUES
('vehicle_class_001', 'ABC'),
('vehicle_class_002', 'BCD'),
('vehicle_class_003', 'EFG');

Attached Images

VEH2.gif (25.0 KB)

]]> Oracle Development deduyay http://www.dbasupport.com/forums/showthread.php?t=62515 What Oracle Services in Oracle Failsafe set to manual http://www.dbasupport.com/forums/showthread.php?t=62514&goto=newpost Thu, 10 May 2012 12:12:21 GMT Can anyone confirm what Oracle Services need to be set to Automatic or Manual in an Oracle Failsafe environment. At the moment we have: Oracleserviceprod - set to MANUAL OracleOraDb11g_home1TNSListenerFslclus - set to MANUAL OracleMSCSService - set to MANUAL I know that the database and the listener is started up by Oracle Failsafe, so needs to be MANUAL, but we are not sure about the Oracle MCSC service. Thanks. Regards, Sheryl Can anyone confirm what Oracle Services need to be set to Automatic or Manual in an Oracle Failsafe environment. At the moment we have:
Oracleserviceprod - set to MANUAL
OracleOraDb11g_home1TNSListenerFslclus - set to MANUAL
OracleMSCSService - set to MANUAL

I know that the database and the listener is started up by Oracle Failsafe, so needs to be MANUAL, but we are not sure about the Oracle MCSC service. Thanks.
Regards, Sheryl ]]> Oracle Database Administration ssmith http://www.dbasupport.com/forums/showthread.php?t=62514 Oracle http://www.dbasupport.com/forums/showthread.php?t=62513&goto=newpost Thu, 10 May 2012 11:34:14 GMT Without using sql loader how to load .csv file into oracle table. Without using sql loader how to load .csv file into oracle table. ]]> Oracle Database Administration kvinokmr89 http://www.dbasupport.com/forums/showthread.php?t=62513 What is the best way to search in a CLOB, in batch operation ? http://www.dbasupport.com/forums/showthread.php?t=62512&goto=newpost Mon, 07 May 2012 09:35:21 GMT 0; ** here - l_snme_sn_temp contains the text that the query searches ( also evident from the where clause, marked **), and is a variable of a TYPE. I am populating l_snme_sn_temp in a chunk of 500 ( using bulk collect limit 500) from a table having at least 1 million records (and the upper cap will not be more than 1.5 M). The procedure that does this, takes 24+ hours to complete. If you guys have any better way to do this or any idea to tune to reduce time kindly suggest. I'd appreciate your kind response. regards, Raj Pandit]]> Hi All,
My application runs batch procedure for searching 'text' into clobs (column notes in the below query) and accordingly inserts into another table-
code snippet is like this -

FORALL i IN 1 .. l_snme_sn_temp.COUNT
INSERT INTO snme_rmanotes_out
(report_request_id, serial_number, scrubbed_sn, product_id, pk1_value, last_update_date, event_date, event)
SELECT pi_report_request_id
, l_snme_sn_temp_orig (i).
, l_snme_sn_temp (i)..
, '' product_id
, pk1_value
, TO_CHAR (last_update_date, 'DD-MON-YYYY') last_update_date
, TO_CHAR (last_update_date, 'DD-MON-YYYY') event_date
, 'IN RMA NOTES' event
FROM c3_rma_notes
WHERE contains (notes, l_snme_sn_temp (i)) > 0; **

here - l_snme_sn_temp contains the text that the query searches ( also evident from the where clause, marked **), and is a variable of a TYPE.
I am populating l_snme_sn_temp in a chunk of 500 ( using bulk collect limit 500) from a table having at least 1 million records (and the upper cap will not be more than 1.5 M).
The procedure that does this, takes 24+ hours to complete.
If you guys have any better way to do this or any idea to tune to reduce time kindly suggest.
I'd appreciate your kind response.
regards,
Raj Pandit ]]> Oracle Development rajkumarpandit http://www.dbasupport.com/forums/showthread.php?t=62512 Question on ASM Views. http://www.dbasupport.com/forums/showthread.php?t=62511&goto=newpost Mon, 07 May 2012 09:29:24 GMT Hi DBA's,

Is it possible to view the V$ asm views when the database is down.If Yes where it is getting the information. Please correct me if i am wrong. ]]> Oracle Database Administration gopu_g http://www.dbasupport.com/forums/showthread.php?t=62511 How to use a cursor...???? http://www.dbasupport.com/forums/showthread.php?t=62510&goto=newpost Thu, 03 May 2012 16:12:00 GMT Hello everyone i'm new to pl/sql and needed some help on cursors i was hoping someone could give me a layout of what i would need e.g. declare exection and exception which i can then try to have a go with the statements thanks even if someone can give me a start e.g. declare section then i can have a go

A description of the Employees and LEAST_EARNERS tables are shown below.

Employees TABLE
Name Null ? Type
empno NOT NULL NUMBER(4)
last_name VARCHAR2(10)
job VARCHAR2(9)
mgr NUMBER(4)
hiredate
salary
DATE
NUMBER(8)

LEAST_EARNERS TABLE
Name Null ? Type
salary NUMBER(8)

Create a PL/SQL block that determines the bottom n salaries of the
employees within an organisation and inserts them into a table called
LEAST_EARNERS. The block should prompt the user to accept a
number n from the user where n represents the number of earners
from the EMPLOYEES table. You should create a cursor called
emp_cursor that retrieves the salaries of employees in ascending
order. The salaries should not be duplicated. In the executable section,
open the cursor and fetch the least n salaries and insert them into the
LEAST_EARNERS table. Include any appropriate exceptions.

thankyou for the help in advance ]]> Oracle Database Administration alankay246 http://www.dbasupport.com/forums/showthread.php?t=62510 Group By Query Help http://www.dbasupport.com/forums/showthread.php?t=62509&goto=newpost Thu, 03 May 2012 15:59:33 GMT I have two tables ‘VEHICLE’ and ‘VEHICLE_CLASS’ as per below: VEHICLE_CLASS ------------------------------------------- VEHICLE_ID | VEHICLE_CLASS_ID | STATUS ------------------------------------------- vehicle_001 | vehicle_class_001 | 0 vehicle_002 | vehicle_class_002 | 1 vehicle_003 | vehicle_class_003 | 2 vehicle_004 | vehicle_class_001 | 0 vehicle_005 | vehicle_class_002 | 2 vehicle_006 | vehicle_class_001 | 0 vehicle_007 | NULL | 1 ---------------------------------------------- VEHICLE ------------------------------------------ VEHICLE_CLASS_ID | VEHICLE_CLASS_NAME ----------------------------------------- vehicle_class_001 | ABC vehicle_class_002 | BCD vehicle_class_003 | EFG vehicle_class_004 | XYZ vehicle_class_005 | PQR vehicle_class_006 | STU --------------------------------------- *There are three statuses 0=Initiated, 1=In Progress, 2=Completed I need a query to get the result rows as per below: ----------------------------------------------------------------------- VEHICLE_CLASS_NAME | COMPLETED_ VEHICLES | NOT_COMPLETED_ VEHICLES --------------------------------------------------------------------------- ABC | |vehicle_001, vehicle_004, vehicle_006 BCD | vehicle_005 |vehicle_002 EFG | vehicle_003 | * If Status=2 then completed else it is not completed. Please see attached image to view tables. Please help me, how can I get result rows. Thank you. I have two tables ‘VEHICLE’ and ‘VEHICLE_CLASS’ as per below:
VEHICLE_CLASS
-------------------------------------------
VEHICLE_ID | VEHICLE_CLASS_ID | STATUS
-------------------------------------------
vehicle_001 | vehicle_class_001 | 0
vehicle_002 | vehicle_class_002 | 1
vehicle_003 | vehicle_class_003 | 2
vehicle_004 | vehicle_class_001 | 0
vehicle_005 | vehicle_class_002 | 2
vehicle_006 | vehicle_class_001 | 0
vehicle_007 | NULL | 1
----------------------------------------------

VEHICLE
------------------------------------------
VEHICLE_CLASS_ID | VEHICLE_CLASS_NAME
-----------------------------------------
vehicle_class_001 | ABC
vehicle_class_002 | BCD
vehicle_class_003 | EFG
vehicle_class_004 | XYZ
vehicle_class_005 | PQR
vehicle_class_006 | STU
---------------------------------------

*There are three statuses 0=Initiated, 1=In Progress, 2=Completed

I need a query to get the result rows as per below:
-----------------------------------------------------------------------
VEHICLE_CLASS_NAME | COMPLETED_ VEHICLES | NOT_COMPLETED_ VEHICLES
---------------------------------------------------------------------------
ABC | |vehicle_001, vehicle_004, vehicle_006
BCD | vehicle_005 |vehicle_002
EFG | vehicle_003 |

* If Status=2 then completed else it is not completed.

Please see attached image to view tables.
Please help me, how can I get result rows.
Thank you.

Attached Images

Veh.gif (26.1 KB)

]]> Oracle Development deduyay http://www.dbasupport.com/forums/showthread.php?t=62509 Archivelog deleted http://www.dbasupport.com/forums/showthread.php?t=62505&goto=newpost Sat, 28 Apr 2012 10:19:57 GMT RMAN 6053 unable to perform media recovery because of missing log --> RMAN 6025 no backup of log thread 1 sequence 1554 lowscn ...... and when I checked the alert log file , I found thouse archives deleted , Deleted Oracle managed file D:\ARCHIVE\mydb\ARCHIVELOG\2012_04_26\O1_MF_1_1554_7SMBB5Y6_.ARC Deleted Oracle managed file D:\ARCHIVE\mydb\ARCHIVELOG\2012_04_26\O1_MF_1_1555_7SMBBDQD_.AR So how it's possible , since I have good space in the FRA What the reasons & assumptions that deleted this file ? Alert Log : Starting control autobackup Fri Apr 27 18:22:57 2012 Errors in file c:\oracle\product\10.2.0\admin\mydb\udump\mydb_ora_3020.trc: Fri Apr 27 18:22:57 2012 Errors in file c:\oracle\product\10.2.0\admin\mydb\mydb_ora_3020.trc: Fri Apr 27 18:22:57 2012 Errors in file c:\oracle\product\10.2.0\admin\mydb\udump\mydb_ora_3020.trc: Control autobackup written to SBT_TAPE device comment 'API Version 2.0,MMS Version 1.2.0.0', media 'DD860HQ1.001.RO' handle 'c-921460516-20120427-00' Fri Apr 27 18:23:11 2012 Thread 1 advanced to log sequence 1586 Current log# 1 seq# 1586 mem# 0: C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\mydb\ONLINELOG\O1_MF_1_7OVXNPSF_.LOG Thread 1 advanced to log sequence 1587 Current log# 3 seq# 1587 mem# 0: C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\mydb\ONLINELOG\O1_MF_3_7OVXO0SF_.LOG Fri Apr 27 18:23:13 2012 Deleted Oracle managed file D:\ARCHIVE\mydb\ARCHIVELOG\2012_04_26\O1_MF_1_1554_7SMBB5Y6_.ARC Deleted Oracle managed file D:\ARCHIVE\mydb\ARCHIVELOG\2012_04_26\O1_MF_1_1555_7SMBBDQD_.ARC Fri Apr 27 18:24:31 2012 Best Regards]]> Dear all,
I have a production DB which is backed up daily including archives , and will be deleted from the server using " delete input " command after backued up.

The problem is when I tried to do restore , I faced those two error ( Rman-6053 - Rman-6025 )
--> RMAN 6053 unable to perform media recovery because of missing log
--> RMAN 6025 no backup of log thread 1 sequence 1554 lowscn ......

and when I checked the alert log file , I found thouse archives deleted ,
Deleted Oracle managed file D:\ARCHIVE\mydb\ARCHIVELOG\2012_04_26\O1_MF_1_1554_7SMBB5Y6_.ARC
Deleted Oracle managed file D:\ARCHIVE\mydb\ARCHIVELOG\2012_04_26\O1_MF_1_1555_7SMBBDQD_.AR

So how it's possible , since I have good space in the FRA

What the reasons & assumptions that deleted this file ?

Alert Log :
Starting control autobackup
Fri Apr 27 18:22:57 2012
Errors in file c:\oracle\product\10.2.0\admin\mydb\udump\mydb_ora_3020.trc:

Fri Apr 27 18:22:57 2012
Errors in file c:\oracle\product\10.2.0\admin\mydb\mydb_ora_3020.trc:

Fri Apr 27 18:22:57 2012
Errors in file c:\oracle\product\10.2.0\admin\mydb\udump\mydb_ora_3020.trc:

Control autobackup written to SBT_TAPE device
comment 'API Version 2.0,MMS Version 1.2.0.0',
media 'DD860HQ1.001.RO'
handle 'c-921460516-20120427-00'
Fri Apr 27 18:23:11 2012
Thread 1 advanced to log sequence 1586
Current log# 1 seq# 1586 mem# 0: C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\mydb\ONLINELOG\O1_MF_1_7OVXNPSF_.LOG
Thread 1 advanced to log sequence 1587
Current log# 3 seq# 1587 mem# 0: C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\mydb\ONLINELOG\O1_MF_3_7OVXO0SF_.LOG
Fri Apr 27 18:23:13 2012
Deleted Oracle managed file D:\ARCHIVE\mydb\ARCHIVELOG\2012_04_26\O1_MF_1_1554_7SMBB5Y6_.ARC
Deleted Oracle managed file D:\ARCHIVE\mydb\ARCHIVELOG\2012_04_26\O1_MF_1_1555_7SMBBDQD_.ARC
Fri Apr 27 18:24:31 2012

Best Regards ]]> Oracle Database Administration mody82 http://www.dbasupport.com/forums/showthread.php?t=62505 SQL loader control file help required http://www.dbasupport.com/forums/showthread.php?t=62504&goto=newpost Fri, 27 Apr 2012 10:47:06 GMT Hi there, I have a text file like below, :20:abc :10:bbc, ccc, 999 :30:bba I need to load the above data in to table like below, 10 20 30 --- --- --- bbcccc,999 abc bba 10, 20, 30 are the columns in the table. Please help me to create a control file for this. Thanks Vinoth Hi there,

I have a text file like below,

:20:abc
:10:bbc,
ccc,
999
:30:bba

I need to load the above data in to table like below,

10 20 30
--- --- ---
bbcccc,999 abc bba

10, 20, 30 are the columns in the table.

Please help me to create a control file for this.

Thanks
Vinoth ]]> Oracle Development vinothjanarthan http://www.dbasupport.com/forums/showthread.php?t=62504