Solved

control file creation and commit

Posted on 2002-04-11
14
615 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
make null the repeated levels 2 37
Checking for column width 8 40
Oracle cursor lifecycle inside procedure. 2 29
Oracle programming for starter 14 37
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

730 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