Solved

control file creation and commit

Posted on 2002-04-11
14
610 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 34

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
 

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 34

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
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.

 
LVL 34

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 34

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 34

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

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.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now