ORA-38760: This database instance failed to turn on flashback database ORA-38701 ORA-27037

Problem Description

Whenever you try to startup your database then it fails with ORA-38760.

SQL> startup
ORACLE instance started.

Total System Global Area 1161966444 bytes
Fixed Size 1787966 bytes
Variable Size 1145750568 bytes
Database Buffers 225163524 bytes
Redo Buffers 1262132 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database.

In the Alert log there goes entry like this.

Errors in file /oracle/admin/agprod/udump/agprod_ora_409616.trc:
ORA-38701: Flashback database log 402 seq 402 thread 1: "/DBarchive/flash_recovery_area/AGPROD/flashback/o1_mf_47cqbhb5_.flb"
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

The analogous of this error is during database running state whenever you delete the current flashbacklog, the database will crash with the following errors:

ORA-38701: Flashback database log 401 seq 401 thread 1:
"/DBarchive/flash_recovery_area/AGPROD/flashback/o1_mf_47cqbhb5_.flb"
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Sat Apr 11 00:03:12 2006
RVWR: terminating instance due to error 38701
Instance terminated by RVWR, pid = 5721

Cause of The Problem
The above errors occurred due to a well identified bug. If there is any I/O error that is preventing write to the flashback logs, the instance will crash. Losing the current flashback log will cause the instance to crash. Also during database startup if flashback logs are inaccessible then instance will crush.

Solution of The Problem
Solution A)-Upgarde Database Version:
This bug is affected from database version 10.1.0.1 to 10.1.0.4
It is fixed in database version 10gR2. So upgrade your database version.

Solution B)-Disable Flashback and restart the instance.
1)If you don't have the situation to upgrade the database then start the database in mount stage.
SQL> startup mount;

2)Turn off the Flashback
SQL> alter database flashback off;

3)Turn on the Flashback
SQL> alter database flashback on;

4)Startup the database
SQL>alter database open;

Or, SQL>shutdown
SQL>startup

Note:
After disabling flashback if you have guaranteed restore point then you still might get the error ORA-38760: This database instance failed to turn on flashback database while starting up the database. In this case you have to drop the guaranteed restore point.

You can see the available guaranteed restore point by,
select NAME,SCN,GUARANTEE_FLASHBACK_DATABASE,DATABASE_INCARNATION# from v$restore_point;
Then drop the guaranteed restore point by,
DROP RESTORE POINT NAME_OF_THE_GUARANTEED_RESTORE_POINT;
Now start your database.

What is View in Oracle

A view in oracle is nothing but a stored sql scripts. View itself contain no data. Whenever we query on a view underlying SQL scripts is executed. Suppose I have created a view as CREATE VIEW TEST_VIEW AS SELECT NAME FROM TEST; Now whenever I query on view TEST_VIEW underlying script SELECT NAME FROM TEST is executed.

A view can be queried, and the data it represents can be changed. Data in a view can be updated or deleted, and new data inserted. These operations directly alter the tables on which the view is based and are subject to the integrity constraints and triggers of the base tables.


Workaround Example:
Connect as a dba user and grant only insert privilege to table TEST on arju schema table to india.


SQL> CONN ARJU/A
Connected.


SQL> revoke SELECT ON TEST from india;
Revoke succeeded.


SQL> grant insert ON TEST TO INDIA;
Grant succeeded.


SQL> CONN INDIA/T
Connected.
SQL> CREATE VIEW ARJU_SCHMEA_VIEW AS SELECT * FROM ARJU.TEST;
View created.


Since india has only insert privilege on the base table so he can create view but can't select any row from the base table or view.


SQL> SELECT * FROM ARJU_SCHMEA_VIEW;
SELECT * FROM ARJU_SCHMEA_VIEW
*
ERROR at line 1:
ORA-01031: insufficient privileges
Now grant him select privilege on the base table.

SQL> CONN ARJU/A
Connected.
SQL> grant SELECT ON TEST TO INDIA;
Grant succeeded.

SQL> CONN INDIA/T
Connected.
SQL> SELECT * FROM ARJU_SCHMEA_VIEW;
no rows selected


Now insert row in to the view.
SQL> INSERT INTO ARJU_SCHMEA_VIEW VALUES(1);
1 row created.


SQL> COMMIT;
Commit complete.


SQL> SELECT * FROM ARJU_SCHMEA_VIEW;
A
----------
1


SQL> SELECT * FROM ARJU.TEST;
A
----------
1


So if underlying view is modified base table is affected.

Benefits of using views

•With views you can restrict database access in both column level and row level. Selective portion of tables can be hidden with view.

•Creating complex view and query on them allow you to make complex query easy. They allow you to make simple queries to retrieve the results from complicated queries.


•Views can be used to allow data independence for ad hoc users and application programs.


•You can present different views of the same data according to their particular criteria.


•For experiment you can just create view and query on them instead of creating temporary tables and dropping them while significantly reduce database fragmentation.

Database Administration Exercises

Dictionary Views to help you work

Using your text and any other sources you wish, make a list of the data dictionary views you feel would be the most helpful to a programmer who is writing applications that perform DML actions on rows from tables in the database. Give a brief explanation for your choice of each view.
Place and save your answers in a Word document named week3_exercise.doc.

Where are my Log Files and Groups?
You have inherited an existing database from a previous Administrator. There is little to no documentation provided for this database and, as you review the database setup, you find you need answers to the following questions:

• What are the redo log group numbers, and how many members does each group have?
• What directory or directories holds the redo log group members?

Write SQL commands or queries to discover all these answers. Place and save your answers in the Word document named week3_exercise.doc that you have started for this week's exercises.

Working with initialization parameters
Use your text and any online documentation you need, determine which of the initialization parameters (many are discussed in chapter 3) are static and which are dynamic. Also determine which can be changed using ALTER SESSION, and which can be changed using ALTER SYSTEM. Make a spread sheet type table with four columns for your lists (one for each category). Some parameters may appear on more than one list and that is acceptable. Use the parameters listed in the book to begin with, and then add some. You should try to have a listing of at least 20 different parameters. Place and save your answers in the Word document named week3_exercise.doc that you have started for this week's exercises.

Error Vmware Failed to lock the file

Problem Description:
If you use vmware then while opening it it failed with following messages,

Cannot open the disk '/home/oracle/win_vmware_image/Windows XP Professional 2.vmdk' or one of the snapshot disks it depends on.
Reason: Failed to lock the file.

The problem can happen with many reasons. Try to solve one by one.

Solution of the Problem:
1)Is you vmware is improperly shutdown? If so look at the directory where vmware resides. Search file there named extention .WRITELOCK. Delete the writelock file and try to start the vmware. If you succeed then no further steps necessary.

2)Do you point to your vmware to another location or add another disk which is not currently available. If so, under poweroff menu >select edit virtual mechine settings >You will see the list of hardware. If you suspect the hardware/disk is not currently available select it and then remove it. Now try to start your vmware.

3)Look for other vmware process or any software is running and whether it use your current existing disk. If so shutdown that process and try to start your vmware.

Different ways to open Office .docx documents

DOCX is the Word document which is the "Microsoft Office Open XML Format Document". This is the new upcoming format for Microsoft Office documents. This extension came with Microsoft Office 2007. It is a combination of XML architecture and ZIP compression for size reduction.

Microsoft introduced the .docx file format in its Office 2007 and wants it to replace the commonly used doc format. Any word document in Office 2007 can be saved as .docx file extension but as .docx extension is latest version, so older version like Office 2000, Office XP, Office 98 are not still aware of the .docx file extension. So these software can't open the .docx file. Though Microsoft promised that .docx file will be compatible to old Microsoft word but they are still their promises.

Now, as many users still using old Microsoft Office so many users can't open the .docx file document. So the main question for these users is of course how they would open a docx file to be able to read and work with it. The answer depends on the platform you are using. Below is some solutions based on platform you are using as well as solution of platform independent.

For Older Version of Microsoft Office:
If you are on windows machine and you have older version of Microsoft Office like Office XP, Office 2000, Office 98 then the best way is to install Microsoft compatibility pack for previous Office versions that adds .docx support in Microsoft Word. You can download the compatibility pack from http://office.microsoft.com/en-us/word/HA100444731033.aspx.

If you use Open office Software:
If you use open office software then you can use the Open XML Translator for Open Office to be able to read and edit docx files.

Online Solution:
There are many websites over Internet that do the conversion job and most of them do offer the job for free. You can upload your .docx file in that site and then you will get back .doc document. There are many docx converter sites for it.

A hack way:
This procedure worked perfectly for Microsoft Office 2007 beta version. But if it is not beta then it will not work. Still you can get an idea about the contents inside your .docx file. The idea is as .docx file is the combination of XML architecture and ZIP compression. So first save the .docx file as .zip and then extract it using any extract utility like unzip. If you used beta version then word folder you will get your expected word file. If you don't you can still have an idea to rename big sized .XML to .HTML and open that file in your browser.

How to know process ID or PID on windows

Process Identifier or Process ID or PID is the unique ID of a process running on the system. With PID we can uniquely identify a process and can know about their characteristics.

On unix based system like linux, solaris, HP-UX with "ps" command we can easily identify each type of process and their PID. We can also grep the output for filtering. But for Windows operating system such as Windows 95, Windows 98, Windows 2000, Windows XP, Windows Vista, Windows Server 2003, Windows Server 2008 and Windows 7 there is no such utility or command.


In windows through GUI we can still see the process ID or PID of a process. This is sometime very essential to track a culprit process. It can be seen from Task Manager. To get Task Manager right click on empty area in the task bar and then you can select Task Manger. You can also get Task Manager window by clicking CTRL+ALT+DEL.


After opening task manager you will see there is no process ID or PID there by default. But you can see Processes tab there. Whenever you click on processes tab, process information is shown. By default option is Image Name(Process Name), User Name(the user under whom process is running), CPU(percentage of CPU usage) and Mem Usage(Total how many KB is allocated to the process).


To get Process ID,
- Click Processes tab.
- Click on View menu, then click on Select Columns.
- In the "Select Columns" or "Select Process Page Columns" dialog, check the checkbox for PID (Process Identifier), and click OK.


You will see PID is shown and you can easily track each process identifier.