Solved

control file creation and commit

Posted on 2002-04-11
14
616 Views
Last Modified: 2008-02-01
Connect as what can we run create controlfile command. Has the database to be in a
particular state while creating controlfile.

commit force 'text' [,SCN]
'text' => transaction_id in dba_2pc_pending view(no data).
SCN => system change number if omitted, current change number is used. How do we find the current SCN.
--- sesh2002
0
Comment
Question by:sesh2002
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 4
14 Comments
 
LVL 4

Accepted Solution

by:
asimkovsky earned 35 total points
ID: 6936826
To create the controlfile, the must be in the nomount mode.  To even put the database in that mode, you must have the SYSDBA role.

Andrew
0
 

Author Comment

by:sesh2002
ID: 6944032
asimovsky, thanx for the first, but you have not answered my second one i.e.
commit force 'text' [,SCN]
'text' => transaction_id in dba_2pc_pending view(no data).
SCN => system change number if omitted, current change number is used. How do we find the current SCN.

--- sesh2002
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 6948195
Do you really need to find the current SCN, or do you need to find the SCN of a particular transaction that was part of a distributed transaction?  That is the only case I know of where you would need to use "commit force".  Check in your alert.log file for the SCN of the transaction that needs to be cleaned up.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:sesh2002
ID: 6965318
markgeer, you answered one more but there is no alert.log file in my oracle directory.
commit force 'text' [,SCN]
'text' => transaction_id in dba_2pc_pending view(no data).
So, what transaction_id can I use.

--- sesh2002
0
 
LVL 4

Expert Comment

by:asimkovsky
ID: 6965580
EVERY oracle database has an alert log. You better look again.


Andrew
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 6966252
Asimkovsky is correct, every Oracle database has an alert log.  The name and location of the file though varies somewhat by operating system, and it is controlled by the init*.ora parameter: background_dump_dest.

Do you actually have a distributed database system with two (or more) Oracle databases on separate servers that each have just some parts of the total system?  That is the only scenario where "commit force" would be needed.

Or do you have just one Oracle database on one server that has an error?
0
 

Author Comment

by:sesh2002
ID: 6967810
markgeer and asimovsky,
One last question remains:
commit force 'text' [,SCN]
'text' => transaction_id in dba_2pc_pending view. There is no data in this view. So what should I substitute for 'text' in this command.

--- sesh2002
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 6968335
If there is no data in that view, then you have no problem that needs "commit force" to solve it, right?

The only situation where "commit force" is needed is with a distributed system.  You haven't told us yet whether you have a distributed database system or not.  Can you clarify that for us please?
0
 

Author Comment

by:sesh2002
ID: 6971310
Markgeer,

We do not have distributed database here. I managed to see the alert.log in place you specified but did not find any SCN or system change number entry in it even for any transaction whatsoever. Also when I opened alert.log in textpad, every min, i get prompt another application has updated file, do you want to reload it. No one is connected to any of the schemas of this database, then how is alert.log growing.

--- sesh2002
0
 
LVL 4

Expert Comment

by:asimkovsky
ID: 6971431
The Oracle database itself is constantly adding to this log.  This is the standard location you would look for most error and informational messages put out by the database.

I would HIGHLY recommend you look through the Oracle Administrator's Guide and learn all that you can about Oracle. Being aware of and monitoring the alert log is one of the very basics of being a DBA. If you are first learning about this file, then this is probably the first time you've looked in it. You could have had literally hundreds of errors in your database and not know it simply because you've never looked.


Andrew
0
 
LVL 4

Expert Comment

by:asimkovsky
ID: 6971442
What Mark was trying to explain was that the only time COMMIT FORCE would need to be used is if a transaction, or part of a transaction in a distributed environment (multiple databases across a network), had crashed or hung or failed, and it needs to be cleaned up. You will NOT see any SCN information in the alert log unless a transaction had a problem.  If you DID see this information in the alert log, it would include the SCN and all relevant information you need to use with COMMIT FORCE.

Bottom line: you don't have the error in your alert log, so you don't need to use COMMIT FORCE.


Andrew
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 6971455
As asimkovsky indicated, Oracle will add data periodically to the alert log whenever the database is open.  For that reason you should set up a process (either manual or automated) to rename the alert log file periodically (I do it every night at midnight with an automated process) so it does not grow to large to work with.

Normally the entries in this file will not include the latest SCN.  That is only included in the alert log under certain error conditions.

Do you have a specific question yet that you are hoping for an answer to?  If so, please restate your question here.  If not, then please accept one of the comments as the answer.
0
 

Author Comment

by:sesh2002
ID: 6972989
Sorry markgeer, difficult to decide but there is no way to split points while awarding. Also what does 'points for you - DrSQL' mean.

--- sesh2002
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 6977553
This site does not allow you to split the points for a question.  If you would like to do that there are two ways:

1. Post a new question in this topic area with a title like "Points for [someone]" and the number of points you feel is appropriate, then accept an answer from that person to that question.

2. Post a question (0 points) in the Customer Support area mentioning the ID# of your question and ask them to give points to two or more people for your question.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question