DBAsupport.com Forums http://www.dbasupport.com/forums/ This is a discussion forum for all issues involved in databases and database development. en Sat, 25 May 2013 08:03:42 GMT vBulletin 60 http://www.dbasupport.com/forums/images/misc/rss.png DBAsupport.com Forums http://www.dbasupport.com/forums/ RMAN restore http://www.dbasupport.com/forums/showthread.php?63003-RMAN-restore&goto=newpost Fri, 24 May 2013 08:23:06 GMT Team, I want to destroy the SYSTEM tablespace and wana do restore and recovery of it using RMAN. Kidly let me know the steps. Team,

I want to destroy the SYSTEM tablespace and wana do restore and recovery of it using RMAN. Kidly let me know the steps. ]]> Oracle Database Administration sushma paturu http://www.dbasupport.com/forums/showthread.php?63003-RMAN-restore connect to DB from sqlplus using script http://www.dbasupport.com/forums/showthread.php?63001-connect-to-DB-from-sqlplus-using-script&goto=newpost Thu, 23 May 2013 19:19:17 GMT conn system/password@testdb but i am looking for something like this sql> conn.sql Enter DB_NAME : testdb (or whatever the DB i am trying to connect) is there a way this can be acheived. Thanks]]> Hi DBA's

i am looking for a script that can be run from sqlplus to connect to multiple databases.
where the script has service name as variable and can prompt me to enter service name everytime i run the script.
i have installed oracle client on the windows machine which is using the tnsnames.ora to connect to the DB's which are on linux.

This task can be achived by typing

sql> conn system/password@testdb

but i am looking for something like this
sql> conn.sql
Enter DB_NAME : testdb (or whatever the DB i am trying to connect)

is there a way this can be acheived.

Thanks ]]> Oracle Database Administration Sultan http://www.dbasupport.com/forums/showthread.php?63001-connect-to-DB-from-sqlplus-using-script User restrication by tablespace or partition http://www.dbasupport.com/forums/showthread.php?62999-User-restrication-by-tablespace-or-partition&goto=newpost Wed, 22 May 2013 22:49:08 GMT I have searched all around, DBASupport as well as google. No luck. This is pretty much my requirement.

1: Schema ABC
2: Two table spaces: 1 - ABC_US 2 - ABC_CA
3: Tables in ABC will be partitioned (2 partitions) on Country_ID: 1 - PRTN_US 2 - PRTN_CA
4: Partition PRTN_US goes to TS ABC_US and partition PRTN_CA goes to TS ABC_CA
5: Data with Country_ID = 'USA' goes to PRTN_US and data with Country_ID = 'CA' goes to PRTN_CA

The main requirement is to create roles ROLE_READ_US and ROLE_READ_CA.
Users ABC_RDR_US and ABC_RDR_CA.

Now, these users have to be restricted to their specific country data. For ex: user ABC_RDR_US should only see the data in table space ABC_US.

How can this be achieved? There is no option to assign roles by table space.

Thanks, in advance ]]> Oracle Database Administration Schin http://www.dbasupport.com/forums/showthread.php?62999-User-restrication-by-tablespace-or-partition Delete Cascasde is not working http://www.dbasupport.com/forums/showthread.php?62997-Delete-Cascasde-is-not-working&goto=newpost Wed, 22 May 2013 18:03:58 GMT rows are deleting from child even we use delete cascade. Please help if anything can be added to delete the child table rows when we delete from parent table. thank you rows are deleting from child even we use delete cascade. Please help if anything can be added to delete the child table rows when we delete from parent table.
thank you ]]> Oracle Database Administration raj007 http://www.dbasupport.com/forums/showthread.php?62997-Delete-Cascasde-is-not-working Listener issue during Data Guard setup http://www.dbasupport.com/forums/showthread.php?62995-Listener-issue-during-Data-Guard-setup&goto=newpost Wed, 22 May 2013 16:54:39 GMT Hello all,

I am trying to setup Oracle 11.2.0.3 Data Guard on ASM on VirtualBox. I have 2 virtual machines running Oracle Linux 6.4. On the first VM where the primary database is (oraprim), there are GRID+Oracle database installed. On the second VM (orastand), there are GRID+ Software Only Oracle installation and I have not run netca. Primary DB is OPEN, Standby DB is in NOMOUNT.

I am currently trying to start the RMAN duplicate from the primary database, but I have trouble connecting to the auxiliary. I get the following error


Code:
[oracle@oraprim ~]$ rman target sys/xxxxx@orcl auxiliary sys/xxxxx@orcls

Recovery Manager: Release 11.2.0.3.0 - Production on Wed May 22 19:39:38 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1343269014)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Please note that I created the tnsnames.ora on the standby server manually because the NETCA did not create it. The lsnrctl status command says "The listener supports no services". However, tnsping between the 2 servers succeeds.


Primary database listener.ora and tnsnames.ora configuration:


Code:
[oracle@oraprim admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.3/db1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENERORCL =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oraprim.localdomain)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )

ADR_BASE_LISTENERORCL = /u01/app/oracle

[oracle@oraprim admin]$
Code:
oracle@oraprim admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.3/db1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oraprim.localdomain)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.oraprim.localdomain)
    )
  )

ORCLS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orastand.localdomain)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcls.orastand.localdomain)
    )
  )

[oracle@oraprim admin]$

Standby database listener.ora and tnsnames.ora configuration:


Code:
[oracle@orastand admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.3/db1/network/admin/listener.ora
# Generated by Oracle configuration tools.

ADR_BASE_LISTENERORCLS = /u01/app/oracle

LISTENERORCLS =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orastand.localdomain)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )

[oracle@orastand admin]$
Code:
[oracle@orastand admin]$ cat tnsnames.ora
# tnsnames.ora
# self-made

ORCLS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orastand.localdomain)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcls.orastand.localdomain)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oraprim.localdomain)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.oraprim.localdomain)
    )
  )
[oracle@orastand admin]$

I would really appreciate any advise on how to fix this issue.

Thank you for your time. ]]> Oracle Database Administration Valkov http://www.dbasupport.com/forums/showthread.php?62995-Listener-issue-during-Data-Guard-setup Recover Data from Oracle 8i http://www.dbasupport.com/forums/showthread.php?62993-Recover-Data-from-Oracle-8i&goto=newpost Wed, 22 May 2013 07:21:45 GMT Hi,

I am facing following problems due to improper shutdown when open my oracle 8i database :

ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\CP\SYSTEM01.DBF

Database mounted successfully but did not open. i have applied following commands:

recover database
recover datafile 'datafile'
RECOVER DATABASE USING BACKUP CONTROLFILE;

in / as sysdba prompt but did not resolve problem. Database is in "no archive mode". Please suggest me any solution to take backup of my database.
I have applied USER tablespace for my user ACC. Can i recover data from USER01.DBF file?

Thanks and regards,

M. Kamran ]]> Oracle Database Administration mrkamran http://www.dbasupport.com/forums/showthread.php?62993-Recover-Data-from-Oracle-8i Just lost all of my data... http://www.dbasupport.com/forums/showthread.php?62989-Just-lost-all-of-my-data...&goto=newpost Tue, 21 May 2013 21:40:39 GMT I guess my database got corrupted and all of my data is showing as corrupted. Even the backup. I contacted Oracle about the situation but there was little they could do. Is there any way to save my information so it isn't all lost? ]]> Oracle Database Administration DBAtotheMAX http://www.dbasupport.com/forums/showthread.php?62989-Just-lost-all-of-my-data... conceptual erd model http://www.dbasupport.com/forums/showthread.php?62985-conceptual-erd-model&goto=newpost Tue, 21 May 2013 17:26:30 GMT Hello guys, I am studding a basic course of database and I was asked to do a project which I could not start because of conceptual erd model. the project asks to create a conceptual and logical ERD diagrams first before moving on to scripts, but i could not translate the text to entities, or well In the questions it says i should have at least 8 entities but i got 6.. if you can help me i would be glad... here is the question.. ---Quote--- Future Events Management Future Events Management is a company based in Bahrain which specialises in organising events around the Kingdom of Bahrain. This includes a wide range of event types, from conferences to music concerts. Once an event is under their management, details of the event are recorded. The event always has a representative (agent) who will work with the management company to coordinate the event. Different events can take place in different locations around Bahrain. An event venue can host different events but not at the same time. Employees of the events management company can work on different events at the same time. Events may be sponsored by sponsors, for example, Batelco and Gulf Air. To sponsor an event, a sponsor should choose a sponsor “package”. The packages involve different benefits for the sponsor and there are three options to choose from which carry a cost. The packages available are: i. Bronze package - includes poster advertising, brand displayed in various locations. It costs 5000BD. ii. Silver package - includes various advertising in different forms, access to VIP areas, brand displayed in many places. It costs 8000BD. iii. Gold package – includes advertising in various locations, brand displayed in all multimedia coverage including TV advertisements. Access to all VIP areas. It costs 10000BD. At the moment, all of this information is recorded manually and it is becoming unmanageable. Therefore, the company wants to create a database which will be used to store past and future events electronically. The following must be considered in your design for the Future Events Management database: • Storing each event with a description. For example; Formula 1 or Placido Domingo: Live in Bahrain. • Dealing with different types of events. For example; conferences, music concerts, etc. • Each event has one agent who acts on behalf of the event and works with Future Events Management to coordinate the event. • Events take place at different venues in various locations in Bahrain. • The company has several employees and an employee can be working on more than one event at any time. • Events can be sponsored by charities, companies or individual people. • Sponsors can choose different sponsor packages at various costs: bronze, silver or gold. Bachelor of Information Communications Technology Page 4 of 8 Question. create Conceptual ERD model diagram.. In this diagram you should include only: entity names, primary key attributes for each entity, relationships between each entity and cardinality (e.g. 1:M, M:N, 1:1) of each relationship. At this stage you should include at least 8 entities. ---End Quote--- thanks alot, Hello guys,

I am studding a basic course of database and I was asked to do a project which I could not start because of conceptual erd model.

the project asks to create a conceptual and logical ERD diagrams first before moving on to scripts, but i could not translate the text to entities, or well In the questions it says i should have at least 8 entities but i got 6..
if you can help me i would be glad...

here is the question..

Quote:
Future Events Management
Future Events Management is a company based in Bahrain which specialises in organising events around the Kingdom of Bahrain. This includes a wide range of event types, from conferences to music concerts. Once an event is under their management, details of the event are recorded. The event always has a representative (agent) who will work with the management company to coordinate the event. Different events can take place in different locations around Bahrain. An event venue can host different events but not at the same time. Employees of the events management company can work on different events at the same time. Events may be sponsored by sponsors, for example, Batelco and Gulf Air. To sponsor an event, a sponsor should choose a sponsor “package”.
The packages involve different benefits for the sponsor and there are three options to choose from which carry a cost.
The packages available are:
i. Bronze package - includes poster advertising, brand displayed in various locations. It costs 5000BD.
ii. Silver package - includes various advertising in different forms, access to VIP areas, brand displayed in many places. It costs 8000BD.
iii. Gold package – includes advertising in various locations, brand displayed in all multimedia coverage including TV advertisements. Access to all VIP areas. It costs 10000BD.

At the moment, all of this information is recorded manually and it is becoming unmanageable. Therefore, the company wants to create a database which will be used to store past and future events electronically. The following must be considered in your design for the Future Events Management database:
• Storing each event with a description. For example; Formula 1 or Placido Domingo: Live in Bahrain.
• Dealing with different types of events. For example; conferences, music concerts, etc.
• Each event has one agent who acts on behalf of the event and works with Future Events Management to coordinate the event.
• Events take place at different venues in various locations in Bahrain.
• The company has several employees and an employee can be working on more than one event at any time.
• Events can be sponsored by charities, companies or individual people.
• Sponsors can choose different sponsor packages at various costs: bronze, silver or gold.
Bachelor of Information Communications Technology Page 4 of 8

Question. create Conceptual ERD model diagram..
In this diagram you should include only: entity names, primary key attributes for each entity, relationships between each entity and cardinality (e.g. 1:M, M:N, 1:1) of each relationship.
At this stage you should include at least 8 entities.
thanks alot, ]]> Oracle : How To xamdkx http://www.dbasupport.com/forums/showthread.php?62985-conceptual-erd-model Table scans from application http://www.dbasupport.com/forums/showthread.php?62983-Table-scans-from-application&goto=newpost Mon, 20 May 2013 18:21:19 GMT Hi,

We have an application where delete statement is taking a long time. When I see the v$session_longops the sql statement is doing a full table scan. But when I do an explain plan on the delete statement it uses the index. The statement is "delete tablename where column_name =:B1". I am new to Oracle any ideas how I should proceed.

Thanks,
SK ]]> Oracle Database Administration kanas http://www.dbasupport.com/forums/showthread.php?62983-Table-scans-from-application Emd_notification.queue_ready http://www.dbasupport.com/forums/showthread.php?62979-Emd_notification.queue_ready&goto=newpost Thu, 16 May 2013 19:21:49 GMT I have seen the DB activity spike occasionally, and when I look at the top sessions I see this running as sys man BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END; The software using the database doesn't appear to be effected (Users are not complaining during the high activity, and this is a user base were they complain over performance for everything else). When it happens it will kick me out of OEM. I have to restart the web browser and log back in. But if I check the status of dbconsole on the UNIX box is states hat it is still running the entire time. netstat shows the OEM port still listening. I did some google searching and found one site say it had to do with sys man not having execute rights to url_smtp and url_tcp, so I granted the rights but it still happened. The database is 10g and it is on a UNIX server. Can anyone give me some advise on resolving this issue, please. Thank you. ]]> Oracle Database Administration jayjabour http://www.dbasupport.com/forums/showthread.php?62979-Emd_notification.queue_ready Sql script that compares two db,s and updates db and insert date of update http://www.dbasupport.com/forums/showthread.php?62977-Sql-script-that-compares-two-db-s-and-updates-db-and-insert-date-of-update&goto=newpost Thu, 16 May 2013 14:46:20 GMT Hi i need some help creating an sql script that can update info from one table in dbase1 to another table in dbase2 that has the same columns and if posible insert date and time in one column when the syncronized is done? Can anyone help mee whit this? Hi i need some help creating an sql script that can update info from one table in dbase1 to another table in dbase2 that has the same columns and if posible insert date and time in one column when the syncronized is done? Can anyone help mee whit this? ]]> Oracle Database Administration miguel_q http://www.dbasupport.com/forums/showthread.php?62977-Sql-script-that-compares-two-db-s-and-updates-db-and-insert-date-of-update Temp Usage http://www.dbasupport.com/forums/showthread.php?62975-Temp-Usage&goto=newpost Thu, 16 May 2013 11:26:31 GMT Hi, I am new to Database activities.Please advise how to check the temp usage and find the query causing that temp in oracle 9i Hi,

I am new to Database activities.Please advise how to check the temp usage and find the query causing that temp in oracle 9i ]]> Oracle Database Administration gargi http://www.dbasupport.com/forums/showthread.php?62975-Temp-Usage Re-create archive logs http://www.dbasupport.com/forums/showthread.php?62971-Re-create-archive-logs&goto=newpost Mon, 13 May 2013 18:40:52 GMT Hello,
10g Standard DB on RHEL 4. I'm wondering if there is a way to re-create or re-generate past archive logs?

Here's the story behind this question:
A few days ago one of my guys started the DB by manually specifying pfile (eg: startup open pfile=/u01/app/oracle/admin/dbs/pfile/init.ora). The problem with that is the pfile specified did not contain all the parameters that are in spfile, namely log_archive_dest_1. I noticed the problem about two hours later and got it restarted correctly, the problem I have now is a gap in archive logs, missing about 25 log files. This gap has caused a problem with applying the archive logs to my standby database. I tried replacing the standby with a newer backup that I assumed wouldn't care about the missing logs but it still is. So I'm wondering if there is a way to remake those arch logs that got skipped or do I need to do an OPEN RESETLOGS?

Thanks, ]]> Oracle Database Administration louie75 http://www.dbasupport.com/forums/showthread.php?62971-Re-create-archive-logs archives not shipping on standby http://www.dbasupport.com/forums/showthread.php?62969-archives-not-shipping-on-standby&goto=newpost Sun, 12 May 2013 07:33:52 GMT Hii all,

I am trying to configure physical standby. Everything was fine but at the end i found that archives from primary db are not shipping to standby..Networking is fine.

Here is the production p file.

db_name=manish
db_unique_name=manish1
#service_name=manish1
remote_login_passwordfile=exclusive
shared_pool_size=102341017
db_block_size=8192
diagnostic_dest=/disk1/oradata/manish
undo_tablespace=undotbs
undo_management=auto
control_files = /disk1/oradata/manish/control.ctl
compatible ='11.2.0'
###################Data guard PARAM's###################
log_archive_dest_1='location=/disk1/oradata/manish/arch
valid_for=(all_logfiles,all_roles)'
log_archive_dest_2='service=stand
valid_for=(online_logfiles,primary_role)
db_unique_name=stand1 lgwr sync affirm reopen=10'
standby_archive_dest=/dsik1/oradata/manish/arch
standby_file_management=auto
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
fal_server=stand
fal_client=manish

here is my standby's p file

db_name=manish
db_unique_name=stand1
remote_login_passwordfile=exclusive
shared_pool_size=102341017
db_block_size=8192
diagnostic_dest=/disk1/oradata/manish
undo_tablespace=undotbs
undo_management=auto
control_files = /disk1/oradata/manish/stand.ctl
compatible ='11.2.0'
###################Data guard PARAM's###################
log_archive_dest_1='location=/disk1/oradata/manish/arch
valid_for=(all_logfiles,all_roles)'
log_archive_dest_2='service=manish lgwr sync affirm reopen=10'
standby_archive_dest=/dsik1/oradata/manish/arch
standby_file_management=auto
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
fal_server=manish
fal_client=stand


archive log list (primary)

archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /disk1/oradata/manish/arch
Oldest online log sequence 198
Next log sequence to archive 199
Current log sequence 199


archive log list(standby)

archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /disk1/oradata/manish/arch
Oldest online log sequence 178
Next log sequence to archive 179
Current log sequence 179

Please help me to get through this ...
Thanks :cool: ]]> Oracle Database Administration manish.sharma http://www.dbasupport.com/forums/showthread.php?62969-archives-not-shipping-on-standby Recover Oracle datafile http://www.dbasupport.com/forums/showthread.php?62967-Recover-Oracle-datafile&goto=newpost Thu, 09 May 2013 13:33:55 GMT Hi all,
unfortunately my hard disk's server crashed, and I do not have any backup,
I recovered oradata folder in crashed hard disk, which is contains DBF, Ora and log files as attachment picture.
My Oracle database version is 10.2.
would you please guid me how can I recover my databse step by step?
Attached Images
]]> Oracle Database Administration saber_talas http://www.dbasupport.com/forums/showthread.php?62967-Recover-Oracle-datafile