Solved

control file creation and commit

Posted on 2002-04-11
14
613 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

776 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